﻿using System;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using System.IO;
using System.Data;
using Newtonsoft.Json.Linq;
using NPOI.XWPF.UserModel;
using System.Collections.Generic;
using NPOI.OpenXmlFormats.Wordprocessing;
using NPOI.HPSF;

namespace Web.Common
{
    public class ExcelTool
    {
        public static DataTable ExcelToDataTable(string fileName)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;

            DataTable data = new DataTable();
            data.Columns.Add("Name");
            data.Columns.Add("Unit");
            data.Columns.Add("BeginTime");
            data.Columns.Add("EndTime");
            data.Columns.Add("Price");
            data.Columns.Add("Type");
            int startRow = 3;
            try
            {
                FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                {
                    workbook = new XSSFWorkbook(fs);

                }
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                {
                    workbook = new HSSFWorkbook(fs);
                }


                sheet = workbook.GetSheetAt(0);

                if (sheet != null)
                {
                    IRow typeRow = sheet.GetRow(1);
                    IRow titleRow = sheet.GetRow(2);
                    int cellCount = titleRow.LastCellNum; //一行最后一个cell的编号 即总的列数



                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    int typecount = cellCount / 5;
                    for (int t = 0; t < typecount; t++)
                    {
                        for (int i = startRow; i <= rowCount; ++i)
                        {
                            IRow row = sheet.GetRow(i);
                            if (row == null) continue; //没有数据的行默认是null　　
                            if (row.GetCell(t * 5) == null) continue; //没有数据的行默认是null　　
                            if (row.GetCell(t * 5).ToString() == "")
                            {
                                continue;
                            }
                            DataRow dataRow = data.NewRow();
                            for (int j = 0; j < 5; ++j)
                            {
                                if (row.GetCell(j + (t * 5)) != null) //同理，没有数据的单元格都默认是null
                                    dataRow[j] = row.GetCell(j + (t * 5)).ToString();
                            }
                            dataRow[5] = typeRow.GetCell(t * 5).ToString();
                            data.Rows.Add(dataRow);
                        }
                    }
                }

                return data;
            }
            catch (Exception ex)
            {
                return data;
            }
        }

        public static DataTable GoodsPriceToDataTable(string fileName)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;

            DataTable data = new DataTable();
            data.Columns.Add("Name");
            data.Columns.Add("Unit");
            data.Columns.Add("BeginTime");
            data.Columns.Add("EndTime");
            data.Columns.Add("Price");
            data.Columns.Add("Type");
            data.Columns.Add("Standard");
            data.Columns.Add("QRCode");
            int startRow = 1;
            try
            {
                FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                {
                    workbook = new HSSFWorkbook(fs);
                }


                sheet = workbook.GetSheetAt(0);

                if (sheet != null)
                {

                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　
                        if (row.GetCell(0) == null) continue; //没有数据的行默认是null　　
                        if (row.GetCell(0).ToString() == "")
                        {
                            continue;
                        }
                        DataRow dataRow = data.NewRow();
                        dataRow[0] = row.GetCell(0) != null ? row.GetCell(0).ToString() : "";
                        dataRow[1] = row.GetCell(1) != null ? row.GetCell(1).ToString() : "";
                        dataRow[2] = row.GetCell(12) != null ? row.GetCell(12).ToString() : "";
                        dataRow[3] = row.GetCell(13) != null ? row.GetCell(13).ToString() : "";
                        dataRow[4] = row.GetCell(4) != null ? row.GetCell(4).ToString() : "";
                        dataRow[5] = "";
                        dataRow[6] = row.GetCell(2) != null ? row.GetCell(02).ToString() : "";
                        dataRow[7] = row.GetCell(5) != null ? row.GetCell(5).ToString() : "";

                        data.Rows.Add(dataRow);
                    }
                }

                return data;
            }
            catch (Exception ex)
            {
                return data;
            }
        }

        public static DataTable GongYiToDataTable(string fileName)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;

            DataTable data = new DataTable();
            data.Columns.Add("ProcedureID");
            data.Columns.Add("GoodsCode");
            data.Columns.Add("GoodsName");
            data.Columns.Add("Stuff");
            data.Columns.Add("BianZhi");
            data.Columns.Add("JiaoDui");
            data.Columns.Add("ProcedureCode");
            data.Columns.Add("ProcedureVersion");
            data.Columns.Add("RowIndex");
            data.Columns.Add("TechnicsName");
            data.Columns.Add("TaskTime");
            data.Columns.Add("Salary");
            data.Columns.Add("CostRate");
            data.Columns.Add("IsReportTime");
            int startRow = 0;
            int procedureid = 0;
            string GoodsCode = "";
            string GoodsName = "";
            string ProcedureCode = "";
            string ProcedureVerson = "";
            string BianZhi = "";
            string JiaoDui = "";
            string Stuff = "";
            try
            {
                FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                {
                    workbook = new HSSFWorkbook(fs);
                }


                sheet = workbook.GetSheetAt(0);

                if (sheet != null)
                {

                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　
                        if (row.GetCell(2) == null) continue; //没有数据的行默认是null　　
                        if (row.GetCell(2).ToString() == "")
                        {
                            continue;
                        }
                        if (row.GetCell(2).ToString() == "工序号")
                        {
                            continue;
                        }
                        if (row.GetCell(2).ToString() == "合计")
                        {
                            continue;
                        }
                        if (row.GetCell(2).ToString() == "零件号")
                        {
                            procedureid = procedureid + 1
; GoodsCode = row.GetCell(3) != null ? row.GetCell(3).ToString() : "";
                            GoodsName = row.GetCell(6) != null ? row.GetCell(6).ToString() : "";
                            continue;
                        }
                        if (row.GetCell(2).ToString() == "工艺编号")
                        {
                            ProcedureCode = row.GetCell(3) != null ? row.GetCell(3).ToString() : "";
                            ProcedureVerson = row.GetCell(15) != null ? row.GetCell(15).ToString() : "";
                            BianZhi = row.GetCell(10) != null ? row.GetCell(10).ToString() : "";
                            JiaoDui = row.GetCell(11) != null ? row.GetCell(11).ToString() : "";
                            Stuff = row.GetCell(6) != null ? row.GetCell(6).ToString() : "";
                            continue;
                        }
                        DataRow dataRow = data.NewRow();
                        dataRow[0] = procedureid;
                        dataRow[1] = GoodsCode;
                        dataRow[2] = GoodsName;
                        dataRow[3] = Stuff;
                        dataRow[4] = BianZhi;
                        dataRow[5] = JiaoDui;
                        dataRow[6] = ProcedureCode;
                        dataRow[7] = ProcedureVerson;
                        dataRow[8] = row.GetCell(2).ToString();
                        dataRow[9] = row.GetCell(3) != null ? row.GetCell(3).ToString() : "";
                        dataRow[10] = row.GetCell(6) != null ? row.GetCell(6).ToString() : "0";
                        dataRow[11] = row.GetCell(7) != null ? row.GetCell(7).ToString() : "0";
                        dataRow[12] = row.GetCell(13) != null ? row.GetCell(13).ToString() : "0";
                        dataRow[13] = row.GetCell(12) != null ? row.GetCell(12).ToString() : "0";

                        data.Rows.Add(dataRow);
                    }
                }

                return data;
            }
            catch (Exception ex)
            {
                return data;
            }
        }

        public static DataTable SaleOrderToDataTable(string fileName)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;

            DataTable data = new DataTable();
            data.Columns.Add("Type");
            data.Columns.Add("GoodsCode");
            data.Columns.Add("GoodsName");
            data.Columns.Add("Qty");
            data.Columns.Add("Number");
            data.Columns.Add("StuffNumber");
            data.Columns.Add("OrderNumber");
            data.Columns.Add("FurnaceNumber");
            data.Columns.Add("Remark");
            data.Columns.Add("DeliveryTime");
            int startRow = 2;
            try
            {
                FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                {
                    workbook = new HSSFWorkbook(fs);
                }


                sheet = workbook.GetSheetAt(0);

                if (sheet != null)
                {

                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) break; //没有数据的行默认是null　　
                        if (row.GetCell(2) == null) break; //没有数据的行默认是null　　
                        if (row.GetCell(2).ToString() == "")
                        {
                            break;
                        }
                        DataRow dataRow = data.NewRow();
                        dataRow[0] = row.GetCell(1) != null ? row.GetCell(1).ToString() : "";
                        dataRow[1] = row.GetCell(2) != null ? row.GetCell(2).ToString() : "";
                        dataRow[2] = row.GetCell(3) != null ? row.GetCell(3).ToString() : "";
                        dataRow[3] = row.GetCell(4) != null ? row.GetCell(4).ToString() : "";
                        dataRow[4] = row.GetCell(5) != null ? row.GetCell(5).ToString() : "";
                        dataRow[5] = row.GetCell(6) != null ? row.GetCell(6).ToString() : "";
                        dataRow[6] = row.GetCell(7) != null ? row.GetCell(7).ToString() : "";
                        dataRow[7] = row.GetCell(8) != null ? row.GetCell(8).ToString() : "";
                        dataRow[8] = row.GetCell(10) != null ? row.GetCell(10).ToString() : "";
                        try
                        {
                            if (row.GetCell(9) == null)
                            {
                                dataRow[9] = DateTime.Now.ToString("yyyy-MM-dd");
                            }
                            else if (row.Cells[9].CellType == CellType.Numeric)
                            {
                                dataRow[9] = row.Cells[9].DateCellValue.ToString("yyyy-MM-dd");
                            }
                            else
                            {
                                dataRow[9] = Convert.ToDateTime(row.GetCell(9).ToString()).ToString("yyyy-MM-dd");
                            }
                        }
                        catch
                        {
                            dataRow[9] = DateTime.Now.ToString("yyyy-MM-dd");
                        }

                        data.Rows.Add(dataRow);
                    }
                }

                return data;
            }
            catch (Exception ex)
            {
                return data;
            }
        }


        public static DataTable GoodsToDataTable(string fileName)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;

            DataTable data = new DataTable();
            data.Columns.Add("Name");
            data.Columns.Add("Model");
            data.Columns.Add("SBillNameID");
            data.Columns.Add("BillNameID");
            int startRow = 1;
            try
            {
                FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                {
                    workbook = new HSSFWorkbook(fs);
                }


                sheet = workbook.GetSheetAt(0);

                if (sheet != null)
                {

                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) break; //没有数据的行默认是null　　
                        if (row.GetCell(0) == null || row.GetCell(1) == null) break; //没有数据的行默认是null　　
                        if (row.GetCell(0).ToString() == "" || row.GetCell(1).ToString() == "")
                        {
                            break;
                        }
                        DataRow dataRow = data.NewRow();
                        dataRow[0] = row.GetCell(0) != null ? row.GetCell(0).ToString() : "";
                        dataRow[1] = row.GetCell(1) != null ? row.GetCell(1).ToString() : "";
                        dataRow[2] = row.GetCell(2) != null ? row.GetCell(2).ToString() : "";
                        dataRow[3] = row.GetCell(3) != null ? row.GetCell(3).ToString() : "";
                        data.Rows.Add(dataRow);
                    }
                }

                return data;
            }
            catch (Exception ex)
            {
                return data;
            }
        }


        public static DataTable ProcedureToDataTable(string fileName)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;

            DataTable data = new DataTable();
            data.Columns.Add("ProcedureID");
            data.Columns.Add("ProjectName");
            data.Columns.Add("GoodsCode");
            data.Columns.Add("GoodsName");
            data.Columns.Add("Stuff");
            data.Columns.Add("ProcedureCode");
            data.Columns.Add("ProcedureVersion");
            data.Columns.Add("RowIndex");
            data.Columns.Add("TechnicsName");
            data.Columns.Add("TaskTime");
            data.Columns.Add("Salary");
            data.Columns.Add("CostRate");
            data.Columns.Add("IsReportTime");
            data.Columns.Add("BianZhi");
            data.Columns.Add("JiaoDui");
            int startRow = 0;
            int procedureid = 0;
            string ProjectName = "";
            string GoodsCode = "";
            string GoodsName = "";
            string Stuff = "";
            string ProcedureCode = "";
            string ProcedureVerson = "";
            string BianZhi = "";
            string JiaoDui = "";

            try
            {
                FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                {
                    workbook = new HSSFWorkbook(fs);
                }


                sheet = workbook.GetSheetAt(0);

                if (sheet != null)
                {

                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　
                        if (row.GetCell(0) == null) continue; //没有数据的行默认是null　　
                        if (row.GetCell(0).ToString() == "")
                        {
                            continue;
                        }
                        if (row.GetCell(0).ToString() == "工序号")
                        {
                            continue;
                        }
                        if (row.GetCell(0).ToString() == "合计")
                        {
                            BianZhi = row.GetCell(4) != null ? row.GetCell(6).ToString() : "";
                            JiaoDui = row.GetCell(2) != null ? row.GetCell(7).ToString() : "";

                            continue;
                        }
                        if (row.GetCell(0).ToString() == "零件号")
                        {
                            procedureid = procedureid + 1;
                            GoodsCode = row.GetCell(1) != null ? row.GetCell(1).ToString() : "";
                            GoodsName = row.GetCell(4) != null ? row.GetCell(4).ToString() : "";
                            ProjectName = row.GetCell(2) != null ? row.GetCell(2).ToString() : "";
                            continue;
                        }
                        if (row.GetCell(0).ToString() == "工艺编号")
                        {
                            ProcedureCode = row.GetCell(1) != null ? row.GetCell(1).ToString() : "";
                            ProcedureVerson = row.GetCell(2) != null ? row.GetCell(2).ToString() : "";
                            Stuff = row.GetCell(4) != null ? row.GetCell(4).ToString() : "";
                            continue;
                        }
                        DataRow dataRow = data.NewRow();
                        dataRow[0] = procedureid;
                        dataRow[1] = ProjectName;
                        dataRow[2] = GoodsCode;
                        dataRow[3] = GoodsName;
                        dataRow[4] = Stuff;
                        dataRow[5] = ProcedureCode;
                        dataRow[6] = ProcedureVerson;
                        dataRow[7] = row.GetCell(0).ToString();
                        dataRow[8] = row.GetCell(1) != null ? row.GetCell(1).ToString() : "";
                        dataRow[9] = row.GetCell(4) != null ? row.GetCell(4).ToString() : "0";
                        dataRow[10] = row.GetCell(5) != null ? row.GetCell(5).ToString() : "0";
                        dataRow[11] = row.GetCell(6) != null ? row.GetCell(6).ToString() : "0";
                        dataRow[12] = row.GetCell(7) != null ? row.GetCell(7).ToString() : "0";
                        dataRow[13] = BianZhi;
                        dataRow[14] = JiaoDui;

                        data.Rows.Add(dataRow);
                    }
                }

                return data;
            }
            catch (Exception ex)
            {
                return data;
            }
        }


        public static MemoryStream Bill1ToExcel(DataTable dt, Models.MVC.Bill1IndexModel m)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 13));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 12, 13));
            IRow rowTitle = sheet1.CreateRow(0);
            rowTitle.Height = 30 * 20;//设置行高
            var cellTitel = rowTitle.CreateCell(0);

            //给标题头添加格式
            ICellStyle styleTitle = hssfworkbook.CreateCellStyle();
            styleTitle.Alignment = HorizontalAlignment.Left;//单元格水平居中
            IFont fontTitle = hssfworkbook.CreateFont();//新建一个字体样式对象
            //fontTitle.IsBold = true;//设置字体加粗样式
            fontTitle.FontHeightInPoints = 20;//设置字体大小
            styleTitle.SetFont(fontTitle);//使用SetFont方法将字体样式添加到单元格样式中
            ICellStyle styleTitle1 = hssfworkbook.CreateCellStyle();
            styleTitle1.CloneStyleFrom(styleTitle);//克隆公共的样式
            styleTitle1.Alignment = HorizontalAlignment.Center;
            cellTitel.CellStyle = styleTitle1;

            cellTitel.SetCellValue("一车间前道工序流程卡");

            //公共样式：加边框
            NPOI.SS.UserModel.ICellStyle style = hssfworkbook.CreateCellStyle();
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            //style.Alignment = HorizontalAlignment.Center;
            IFont font = hssfworkbook.CreateFont();
            font.FontHeightInPoints = 12;
            style.SetFont(font);
            style.VerticalAlignment = VerticalAlignment.Center;
            //表头样式
            NPOI.SS.UserModel.ICellStyle styleHeader = hssfworkbook.CreateCellStyle();
            styleHeader.CloneStyleFrom(style);//克隆公共的样式
            styleHeader.Alignment = HorizontalAlignment.Center;//单元格水平居中
            IFont fontHeader = hssfworkbook.CreateFont();//新建一个字体样式对象
            fontHeader.IsBold = true;//设置字体加粗样式
            fontHeader.FontHeightInPoints = 12;//设置字体加粗样式
            styleHeader.SetFont(fontHeader);//使用SetFont方法将字体样式添加到单元格样式中

            IRow rowHeader1 = sheet1.CreateRow(1);
            rowHeader1.Height = 25 * 20;//设置行高

            string[] title1 = new string[] {
               "型号",
               m.GoodsModel,
               "名称",
               m.GoodsName,
               "卡号",
               m.Code,
               "硅片型号",
               m.SourceGoodsModel,
               "硅片名称",
               m.SourceGoodsName,
               "电阻率",
               m.DianZhu,
               "货源号",
               m.HuoYuanHao
            };

            for (int i = 0; i < 14; i++)
            {
                var cellHeard = rowHeader1.CreateCell(i);
                cellHeard.CellStyle = styleHeader;
                cellHeard.SetCellValue(title1[i]);
            }

            IRow rowHeader2 = sheet1.CreateRow(2);
            rowHeader2.Height = 25 * 20;//设置行高
            string[] title2 = new string[] {
                "领料日期",
                m.LingDate,
                "领料人",
                m.LingMan,
                "投片日期",
                m.TouDate,
                "投片负责人",
                m.TouMan,
                "产品序号",
                m.Number,
                "投片数量",
                m.TouQty.ToString(),
                "检验号",
                m.JianYanHao
            };

            for (int i = 0; i < 14; i++)
            {
                var cellHeard = rowHeader2.CreateCell(i);
                cellHeard.CellStyle = styleHeader;
                cellHeard.SetCellValue(title2[i]);
            }

            IRow rowHeader = sheet1.CreateRow(3);
            rowHeader.Height = 25 * 20;//设置行高

            string[] title = new string[] {
               "工序",
               "接收数",
               "下传数",
               "废品数",
               "废片原因",
               "工时(小时)",
               "工艺条件",
               "操作者",
               "生产日期",
               "检查员",
               "温度",
               "湿度",
               "备注",
               "",
            };

            for (int i = 0; i < 14; i++)
            {
                var cellHeard = rowHeader.CreateCell(i);
                cellHeard.CellStyle = styleHeader;
                cellHeard.SetCellValue(title[i]);
            }


            //表体数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i + 4, i + 4, 12, 13));
                IRow rowtemp = sheet1.CreateRow(i + 4);
                rowtemp.Height = 20 * 20;//设置行高

                for (int j = 0; j < 14; j++)
                {
                    var cell = rowtemp.CreateCell(j);
                    cell.CellStyle = style;
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }

            for (int j = 0; j < 13; j++)
            {
                sheet1.AutoSizeColumn(j);
            }


            System.IO.MemoryStream file = new System.IO.MemoryStream();
            hssfworkbook.Write(file);
            file.Seek(0, System.IO.SeekOrigin.Begin);

            return file;

        }
        public static MemoryStream Bill3ToExcel(DataTable dt, Models.MVC.Bill3IndexModel m)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 9));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 9));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 0, 9));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 0, 9));

            //给标题头添加格式
            ICellStyle styleTitle = hssfworkbook.CreateCellStyle();
            styleTitle.Alignment = HorizontalAlignment.Center;//单元格水平居中
            IFont fontTitle = hssfworkbook.CreateFont();//新建一个字体样式对象
            //fontTitle.IsBold = true;//设置字体加粗样式
            fontTitle.FontHeightInPoints = 12;//设置字体大小
            styleTitle.SetFont(fontTitle);//使用SetFont方法将字体样式添加到单元格样式中
            styleTitle.VerticalAlignment = VerticalAlignment.Center;
            styleTitle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            styleTitle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            styleTitle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            styleTitle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;


            ICellStyle styleTitle0 = hssfworkbook.CreateCellStyle();
            IFont fontTitle0 = hssfworkbook.CreateFont();//新建一个字体样式对象
            fontTitle0.IsBold = true;//设置字体加粗样式
            fontTitle0.FontHeightInPoints = 20;//设置字体大小
            styleTitle0.SetFont(fontTitle0);//使用SetFont方法将字体样式添加到单元格样式中
            styleTitle0.Alignment = HorizontalAlignment.Center;
            styleTitle0.VerticalAlignment = VerticalAlignment.Center;

            ICellStyle styleTitle1 = hssfworkbook.CreateCellStyle();
            styleTitle1.CloneStyleFrom(styleTitle);//克隆公共的样式
            styleTitle1.Alignment = HorizontalAlignment.Right;
            styleTitle1.VerticalAlignment = VerticalAlignment.Center;


            //公共样式：加边框
            NPOI.SS.UserModel.ICellStyle style = hssfworkbook.CreateCellStyle();
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            //style.Alignment = HorizontalAlignment.Center;
            IFont font = hssfworkbook.CreateFont();
            font.FontHeightInPoints = 12;
            style.SetFont(font);
            style.VerticalAlignment = VerticalAlignment.Center;
            style.Alignment = HorizontalAlignment.Center;//单元格水平居中

            //表头样式
            NPOI.SS.UserModel.ICellStyle styleHeader = hssfworkbook.CreateCellStyle();
            styleHeader.CloneStyleFrom(style);//克隆公共的样式
            styleHeader.Alignment = HorizontalAlignment.Center;//单元格水平居中
            IFont fontHeader = hssfworkbook.CreateFont();//新建一个字体样式对象
            fontHeader.IsBold = true;//设置字体加粗样式
            fontHeader.FontHeightInPoints = 12;//设置字体加粗样式
            styleHeader.SetFont(fontHeader);//使用SetFont方法将字体样式添加到单元格样式中
            styleHeader.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            styleHeader.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            styleHeader.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            styleHeader.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            styleHeader.Alignment = HorizontalAlignment.Center;//单元格水平居中
            styleHeader.VerticalAlignment = VerticalAlignment.Center;


            IRow rowJiHuaHao0 = sheet1.CreateRow(0);
            rowJiHuaHao0.Height = 30 * 20;//设置行高
            var cellJiHuaHao0 = rowJiHuaHao0.CreateCell(0);
            cellJiHuaHao0.CellStyle = styleTitle1;
            cellJiHuaHao0.SetCellValue("Q/CY.2.75.01　　");
            IRow rowJiHuaHao1 = sheet1.CreateRow(1);
            rowJiHuaHao1.Height = 40 * 20;//设置行高
            var cellJiHuaHao1 = rowJiHuaHao1.CreateCell(0);
            cellJiHuaHao1.CellStyle = styleTitle0;
            cellJiHuaHao1.SetCellValue("生产作业计划");

            IRow rowJiHuaHao = sheet1.CreateRow(2);
            rowJiHuaHao.Height = 30 * 20;//设置行高
            var cellJiHuaHao = rowJiHuaHao.CreateCell(0);
            cellJiHuaHao.CellStyle = styleTitle1;
            cellJiHuaHao.SetCellValue("计划号：" + m.JiHuaHao + "　　　　批号：" + m.Number + "　　");

            IRow rowTitle = sheet1.CreateRow(3);
            rowTitle.Height = 30 * 20;//设置行高
            var cellTitel = rowTitle.CreateCell(0);
            cellTitel.CellStyle = styleTitle;
            cellTitel.SetCellValue("申请材料领用单");

            for (int i = 1; i < 10; i++)
            {
                var cellHeard = rowTitle.CreateCell(i);
                cellHeard.CellStyle = style;
                cellHeard.SetCellValue("");
            }


            IRow row1 = sheet1.CreateRow(4);
            row1.Height = 25 * 20;//设置行高

            string[] title1 = new string[] {
               "管座型号",
               "货源号",
               "检验批号",
               "制造厂商",
               "领取数量",
               "管帽型号",
               "货源号",
               "检验批号",
               "制造厂商",
               "领取数量",
            };

            for (int i = 0; i < 10; i++)
            {
                var cellHeard = row1.CreateCell(i);
                cellHeard.CellStyle = styleHeader;
                cellHeard.SetCellValue(title1[i]);
            }

            IRow row2 = sheet1.CreateRow(5);
            row2.Height = 25 * 20;//设置行高
            string[] title2 = new string[] {
                dt.Rows.Count>0? dt.Rows[0]["GoodsModel"].ToString():"",
                dt.Rows.Count>0? dt.Rows[0]["HuoYuanHao"].ToString():"",
                dt.Rows.Count>0? dt.Rows[0]["JianYanPiHao"].ToString():"",
                dt.Rows.Count>0? dt.Rows[0]["ZhiZhaoChangShang"].ToString():"",
                dt.Rows.Count>0? dt.Rows[0]["LingQty"].ToString():"",
                dt.Rows.Count>1? dt.Rows[1]["GoodsModel"].ToString():"",
                dt.Rows.Count>1? dt.Rows[1]["HuoYuanHao"].ToString():"",
                dt.Rows.Count>1? dt.Rows[1]["JianYanPiHao"].ToString():"",
                dt.Rows.Count>1? dt.Rows[1]["ZhiZhaoChangShang"].ToString():"",
                dt.Rows.Count>0? dt.Rows[1]["LingQty"].ToString():"",
            };

            for (int i = 0; i < 10; i++)
            {
                var cellHeard = row2.CreateCell(i);
                cellHeard.CellStyle = style;
                cellHeard.SetCellValue(title2[i]);
            }
            IRow row3 = sheet1.CreateRow(6);
            row3.Height = 25 * 20;//设置行高

            string[] title3 = new string[] {
               "芯片型号",
               "芯片面积",
               "正面金属",
               "背面金属",
               "货源号",
               "检验批号",
               "制造厂商",
               "领取数量",
               "镜检淘汰数",
               "",
            };

            for (int i = 0; i < 10; i++)
            {
                var cellHeard = row3.CreateCell(i);
                cellHeard.CellStyle = styleHeader;
                cellHeard.SetCellValue(title3[i]);
            }
            for (int x = 2; x < dt.Rows.Count; x++)
            {


                IRow row4 = sheet1.CreateRow(5 + x);
                row4.Height = 25 * 20;//设置行高
                string[] title4 = new string[] {
                dt.Rows[x]["GoodsModel"].ToString(),
                dt.Rows[x]["XinPianMianJi"].ToString(),
                dt.Rows[x]["ZhengMianJinShu"].ToString(),
                dt.Rows[x]["BeiMianJinShu"].ToString(),
                dt.Rows[x]["HuoYuanHao"].ToString(),
                dt.Rows[x]["JianYanPiHao"].ToString(),
                dt.Rows[x]["ZhiZhaoChangShang"].ToString(),
                dt.Rows[x]["LingQty"].ToString(),
                "",
                ""
                };
                for (int i = 0; i < 10; i++)
                {
                    var cellHeard = row4.CreateCell(i);
                    cellHeard.CellStyle = style;
                    cellHeard.SetCellValue(title4[i]);
                }
            }
            if (dt.Rows.Count < 3)
            {
                IRow row4 = sheet1.CreateRow(7);
                row4.Height = 25 * 20;//设置行高
                string[] title4 = new string[] {
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                ""
                };
                for (int i = 0; i < 10; i++)
                {
                    var cellHeard = row4.CreateCell(i);
                    cellHeard.CellStyle = style;
                    cellHeard.SetCellValue(title4[i]);
                }
            }

            IRow row5 = sheet1.CreateRow(dt.Rows.Count <= 3 ? 8 : dt.Rows.Count + 5);
            row5.Height = 25 * 20;//设置行高

            string[] title5 = new string[] {
               "合同编号",
               "接单日期",
               "产品名称",
               "产品型号",
               "需方代号",
               "质量等级",
               "封装外型",
               "合同数量",
               "计划数量",
               "交付日期",
            };

            for (int i = 0; i < 10; i++)
            {
                var cellHeard = row5.CreateCell(i);
                cellHeard.CellStyle = styleHeader;
                cellHeard.SetCellValue(title5[i]);
            }

            IRow row6 = sheet1.CreateRow(dt.Rows.Count <= 3 ? 9 : dt.Rows.Count + 6);
            row6.Height = 25 * 20;//设置行高

            string[] title6 = new string[] {
               m.HeTongCode,
               m.CreateDate,
               m.GoodsName,
               m.GoodsModel,
               m.XuFangDaiHao,
               m.DengJi,
               m.WaiXing,
               m.HeTongCount.ToString("G0"),
               m.JiHuaQty.ToString("G0"),
               m.JiaoFuDate,
            };

            for (int i = 0; i < 10; i++)
            {
                var cellHeard = row6.CreateCell(i);
                cellHeard.CellStyle = style;
                cellHeard.SetCellValue(title6[i]);
            }

            IRow row7 = sheet1.CreateRow(dt.Rows.Count <= 3 ? 10 : dt.Rows.Count + 7);
            row7.Height = 25 * 20;//设置行高
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(dt.Rows.Count <= 3 ? 10 : dt.Rows.Count + 7, dt.Rows.Count <= 3 ? 10 : dt.Rows.Count + 7, 0, 9));
            var cellJiShuYaoQiu = row7.CreateCell(0);
            cellJiShuYaoQiu.CellStyle = styleTitle;
            cellJiShuYaoQiu.SetCellValue("技术要求");

            for (int i = 1; i < 10; i++)
            {
                var cellHeard = row7.CreateCell(i);
                cellHeard.CellStyle = style;
                cellHeard.SetCellValue("");
            }


            IRow row8 = sheet1.CreateRow(dt.Rows.Count <= 3 ? 11 : dt.Rows.Count + 8);
            row8.Height = 25 * 20;//设置行高
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(dt.Rows.Count <= 3 ? 11 : dt.Rows.Count + 8, dt.Rows.Count <= 3 ? 11 : dt.Rows.Count + 8, 0, 2));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(dt.Rows.Count <= 3 ? 11 : dt.Rows.Count + 8, dt.Rows.Count <= 3 ? 11 : dt.Rows.Count + 8, 3, 6));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(dt.Rows.Count <= 3 ? 11 : dt.Rows.Count + 8, dt.Rows.Count <= 3 ? 11 : dt.Rows.Count + 8, 7, 9));
            var cellrow8 = row8.CreateCell(0);
            cellrow8.CellStyle = style;
            cellrow8.SetCellValue("详细规范号");
            var cellrow81 = row8.CreateCell(3);
            cellrow81.CellStyle = style;
            cellrow81.SetCellValue("监制/验收");
            var cellrow82 = row8.CreateCell(7);
            cellrow82.CellStyle = style;
            cellrow82.SetCellValue("其它要求");

            for (int i = 1; i < 3; i++)
            {
                var cellHeard = row8.CreateCell(i);
                cellHeard.CellStyle = style;
                cellHeard.SetCellValue("");
            }
            for (int i = 4; i < 7; i++)
            {
                var cellHeard = row8.CreateCell(i);
                cellHeard.CellStyle = style;
                cellHeard.SetCellValue("");
            }
            for (int i = 8; i < 10; i++)
            {
                var cellHeard = row8.CreateCell(i);
                cellHeard.CellStyle = style;
                cellHeard.SetCellValue("");
            }


            IRow row9 = sheet1.CreateRow(dt.Rows.Count <= 3 ? 12 : dt.Rows.Count + 9);
            row9.Height = 25 * 20;//设置行高
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(dt.Rows.Count <= 3 ? 12 : dt.Rows.Count + 9, dt.Rows.Count <= 3 ? 12 : dt.Rows.Count + 9, 0, 2));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(dt.Rows.Count <= 3 ? 12 : dt.Rows.Count + 9, dt.Rows.Count <= 3 ? 12 : dt.Rows.Count + 9, 3, 6));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(dt.Rows.Count <= 3 ? 12 : dt.Rows.Count + 9, dt.Rows.Count <= 3 ? 12 : dt.Rows.Count + 9, 7, 9));
            var cellrow9 = row9.CreateCell(0);
            cellrow9.CellStyle = style;
            cellrow9.SetCellValue(m.GuiFanHao);
            var cellrow91 = row9.CreateCell(3);
            cellrow91.CellStyle = style;
            cellrow91.SetCellValue(m.JianZhiYanShou);
            var cellrow92 = row9.CreateCell(7);
            cellrow92.CellStyle = style;
            cellrow92.SetCellValue(m.QiTa);

            for (int i = 1; i < 3; i++)
            {
                var cellHeard = row9.CreateCell(i);
                cellHeard.CellStyle = style;
                cellHeard.SetCellValue("");
            }
            for (int i = 4; i < 7; i++)
            {
                var cellHeard = row9.CreateCell(i);
                cellHeard.CellStyle = style;
                cellHeard.SetCellValue("");
            }
            for (int i = 8; i < 10; i++)
            {
                var cellHeard = row9.CreateCell(i);
                cellHeard.CellStyle = style;
                cellHeard.SetCellValue("");
            }


            IRow row10 = sheet1.CreateRow(dt.Rows.Count <= 3 ? 13 : dt.Rows.Count + 10);
            row10.Height = 25 * 20;//设置行高
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(dt.Rows.Count <= 3 ? 13 : dt.Rows.Count + 10, dt.Rows.Count <= 3 ? 13 : dt.Rows.Count + 10, 0, 9));
            var cellrow10 = row10.CreateCell(0);
            cellrow10.CellStyle = styleTitle;
            cellrow10.SetCellValue("投产信息");
            for (int i = 1; i < 10; i++)
            {
                var cellHeard = row10.CreateCell(i);
                cellHeard.CellStyle = style;
                cellHeard.SetCellValue("");
            }


            IRow rowHeader = sheet1.CreateRow(dt.Rows.Count <= 3 ? 14 : dt.Rows.Count + 11);
            rowHeader.Height = 25 * 20;//设置行高

            string[] title = new string[] {
               "下传型号",
               "下传日期",
               "流程卡号",
               "下传数量",
               "废品数量",
               "下传部门",
               "",
               "签字",
               "备注",
               "",
            };

            for (int i = 0; i < 10; i++)
            {
                var cellHeard = rowHeader.CreateCell(i);
                cellHeard.CellStyle = styleHeader;
                cellHeard.SetCellValue(title[i]);
            }

            IRow rowHeader1 = sheet1.CreateRow(dt.Rows.Count <= 3 ? 15 : dt.Rows.Count + 12);
            rowHeader1.Height = 25 * 20;//设置行高

            string[] title13 = new string[] {
               m.JiaoFuModel,
               m.JiaoFuDate,
               m.Code,
               String.IsNullOrWhiteSpace(m.Code)?"": m.JiaoFuQty.ToString(),
               String.IsNullOrWhiteSpace(m.Code)?"":m.FailQty.ToString(),
               "",
               "",
               "",
               "",
               "",
            };

            for (int i = 0; i < 10; i++)
            {
                var cellHeard = rowHeader1.CreateCell(i);
                cellHeard.CellStyle = style;
                cellHeard.SetCellValue(title13[i]);
            }

            IRow rowHeader2 = sheet1.CreateRow(dt.Rows.Count <= 3 ? 16 : dt.Rows.Count + 13);
            rowHeader2.Height = 25 * 20;//设置行高

            string[] title14 = new string[] {
               "入库型号",
               "接收日期",
               "流程卡号",
               "销售库数量",
               "半成品库数量",
               "在产数量",
               "废品数量",
               "签字",
               "备注",
               "",
            };

            for (int i = 0; i < 10; i++)
            {
                var cellHeard = rowHeader2.CreateCell(i);
                cellHeard.CellStyle = styleHeader;
                cellHeard.SetCellValue(title14[i]);
            }

            IRow rowHeader3 = sheet1.CreateRow(dt.Rows.Count <= 3 ? 17 : dt.Rows.Count + 14);
            rowHeader3.Height = 25 * 20;//设置行高

            string[] title15 = new string[] {
               m.JiaoFuModel,
               m.JiaoFuDate,
               m.Code,
               "",
               "",
               "",
               String.IsNullOrWhiteSpace(m.Code)?"":m.FailQty.ToString(),
               "",
               "",
               "",
            };

            for (int i = 0; i < 10; i++)
            {
                var cellHeard = rowHeader3.CreateCell(i);
                cellHeard.CellStyle = style;
                cellHeard.SetCellValue(title15[i]);
            }

            IRow rowHeader4 = sheet1.CreateRow(dt.Rows.Count <= 3 ? 18 : dt.Rows.Count + 15);
            rowHeader4.Height = 25 * 20;//设置行高

            string[] title16 = new string[] {
               "编制：",
               "",
               "",
               "",
               "审批：",
               "",
               "",
               "",
               "接收：",
               "",
            };

            for (int i = 0; i < 10; i++)
            {
                var cellHeard = rowHeader4.CreateCell(i);
                //cellHeard.CellStyle = style;
                cellHeard.CellStyle.SetFont(font);
                cellHeard.SetCellValue(title16[i]);
            }



            for (int j = 0; j < 10; j++)
            {
                sheet1.AutoSizeColumn(j);
            }


            System.IO.MemoryStream file = new System.IO.MemoryStream();
            hssfworkbook.Write(file);
            file.Seek(0, System.IO.SeekOrigin.Begin);

            return file;

        }

        public static MemoryStream Bill3ToExcel2(DataTable dt, Models.MVC.Bill3IndexModel m)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 5));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 6, 7));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 7));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 0, 1));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 2, 3));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 4, 5));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 6, 7));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 0, 1));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 2, 3));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 4, 7));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 0, 1));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 2, 3));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 4, 7));

            IFont fontTitle = hssfworkbook.CreateFont();//新建一个字体样式对象
            fontTitle.IsBold = true;//设置字体加粗样式
            fontTitle.FontHeightInPoints = 14;//设置字体大小
            IFont fontTitle2 = hssfworkbook.CreateFont();//新建一个字体样式对象
            fontTitle2.IsBold = true;//设置字体加粗样式
            fontTitle2.FontHeightInPoints = 16;//设置字体大小
            IFont font = hssfworkbook.CreateFont();
            font.FontHeightInPoints = 12;
            //给标题头添加格式
            ICellStyle styleTitle = hssfworkbook.CreateCellStyle();
            styleTitle.Alignment = HorizontalAlignment.Center;//单元格水平居中
            styleTitle.VerticalAlignment = VerticalAlignment.Center;
            styleTitle.SetFont(fontTitle);//使用SetFont方法将字体样式添加到单元格样式中
            ICellStyle styleTitle3 = hssfworkbook.CreateCellStyle();
            styleTitle3.Alignment = HorizontalAlignment.Right;//单元格水平居中
            styleTitle3.VerticalAlignment = VerticalAlignment.Center;
            styleTitle3.SetFont(font);//使用SetFont方法将字体样式添加到单元格样式中
            ICellStyle styleTitle31 = hssfworkbook.CreateCellStyle();
            styleTitle31.Alignment = HorizontalAlignment.Left;//单元格水平居中
            styleTitle31.VerticalAlignment = VerticalAlignment.Center;
            styleTitle31.SetFont(font);//使用SetFont方法将字体样式添加到单元格样式中

            ICellStyle styleTitle311 = hssfworkbook.CreateCellStyle();
            styleTitle311.Alignment = HorizontalAlignment.Left;//单元格水平居中
            styleTitle311.VerticalAlignment = VerticalAlignment.Center;
            styleTitle311.SetFont(font);//使用SetFont方法将字体样式添加到单元格样式中
            //styleTitle311.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            styleTitle311.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            //styleTitle311.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            styleTitle311.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

            ICellStyle styleTitle312 = hssfworkbook.CreateCellStyle();
            styleTitle312.Alignment = HorizontalAlignment.Left;//单元格水平居中
            styleTitle312.VerticalAlignment = VerticalAlignment.Center;
            styleTitle312.SetFont(font);//使用SetFont方法将字体样式添加到单元格样式中
            //styleTitle312.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            //styleTitle312.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            styleTitle312.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            styleTitle312.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

            ICellStyle styleTitle313 = hssfworkbook.CreateCellStyle();
            styleTitle313.Alignment = HorizontalAlignment.Left;//单元格水平居中
            styleTitle313.VerticalAlignment = VerticalAlignment.Center;
            styleTitle313.SetFont(font);//使用SetFont方法将字体样式添加到单元格样式中
            styleTitle313.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            styleTitle313.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            //styleTitle313.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            //styleTitle313.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

            ICellStyle styleTitle314 = hssfworkbook.CreateCellStyle();
            styleTitle314.Alignment = HorizontalAlignment.Left;//单元格水平居中
            styleTitle314.VerticalAlignment = VerticalAlignment.Center;
            styleTitle314.SetFont(font);//使用SetFont方法将字体样式添加到单元格样式中
            styleTitle314.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            //styleTitle314.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            styleTitle314.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            //styleTitle314.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

            ICellStyle styleTitle315 = hssfworkbook.CreateCellStyle();
            styleTitle315.Alignment = HorizontalAlignment.Left;//单元格水平居中
            styleTitle315.VerticalAlignment = VerticalAlignment.Center;
            styleTitle315.SetFont(font);//使用SetFont方法将字体样式添加到单元格样式中
            //styleTitle315.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            //styleTitle315.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            //styleTitle315.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            styleTitle315.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

            ICellStyle styleTitle316 = hssfworkbook.CreateCellStyle();
            styleTitle316.Alignment = HorizontalAlignment.Left;//单元格水平居中
            styleTitle316.VerticalAlignment = VerticalAlignment.Center;
            styleTitle316.SetFont(font);//使用SetFont方法将字体样式添加到单元格样式中
            styleTitle316.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            //styleTitle316.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            //styleTitle316.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            //styleTitle316.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

            ICellStyle styleTitle1 = hssfworkbook.CreateCellStyle();
            styleTitle1.CloneStyleFrom(styleTitle);//克隆公共的样式
            styleTitle1.Alignment = HorizontalAlignment.Center;
            styleTitle1.VerticalAlignment = VerticalAlignment.Center;
            styleTitle1.SetFont(fontTitle);

            ICellStyle styleTitle2 = hssfworkbook.CreateCellStyle();
            styleTitle2.CloneStyleFrom(styleTitle);//克隆公共的样式
            styleTitle2.Alignment = HorizontalAlignment.Center;
            styleTitle2.VerticalAlignment = VerticalAlignment.Center;
            styleTitle2.SetFont(fontTitle2);

            //公共样式：加边框
            NPOI.SS.UserModel.ICellStyle style = hssfworkbook.CreateCellStyle();
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            //style.Alignment = HorizontalAlignment.Center;
            style.SetFont(font);
            style.VerticalAlignment = VerticalAlignment.Center;
            style.Alignment = HorizontalAlignment.Center;//单元格水平居中
            style.WrapText = true;
            //表头样式
            NPOI.SS.UserModel.ICellStyle styleHeader = hssfworkbook.CreateCellStyle();
            styleHeader.CloneStyleFrom(style);//克隆公共的样式
            styleHeader.Alignment = HorizontalAlignment.Center;//单元格水平居中
            IFont fontHeader = hssfworkbook.CreateFont();//新建一个字体样式对象
            fontHeader.IsBold = true;//设置字体加粗样式
            fontHeader.FontHeightInPoints = 12;//设置字体加粗样式
            styleHeader.SetFont(fontHeader);//使用SetFont方法将字体样式添加到单元格样式中
            styleHeader.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            styleHeader.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            styleHeader.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            styleHeader.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            styleHeader.Alignment = HorizontalAlignment.Center;//单元格水平居中
            styleHeader.VerticalAlignment = VerticalAlignment.Center;
            styleHeader.WrapText = true;

            IRow rowJiHuaHao = sheet1.CreateRow(0);
            rowJiHuaHao.Height = 30 * 20;//设置行高
            var cellJiHuaHao = rowJiHuaHao.CreateCell(0);
            cellJiHuaHao.CellStyle = styleTitle1;
            cellJiHuaHao.SetCellValue("朝阳微电子科技股份有限公司");
            var cellJiHuaHao6 = rowJiHuaHao.CreateCell(6);
            cellJiHuaHao6.CellStyle = styleTitle1;
            cellJiHuaHao6.SetCellValue(m.Code);

            IRow rowTitle = sheet1.CreateRow(1);
            rowTitle.Height = 30 * 20;//设置行高
            var cellTitel = rowTitle.CreateCell(0);
            cellTitel.CellStyle = styleTitle2;
            cellTitel.SetCellValue("封装流程卡");

            IRow row2 = sheet1.CreateRow(2);
            var row2cell0 = row2.CreateCell(0);
            row2cell0.CellStyle = styleTitle3;
            row2cell0.SetCellValue("产品型号：");

            var row2cell2 = row2.CreateCell(2);
            row2cell2.CellStyle = styleTitle31;
            row2cell2.SetCellValue(m.GoodsModel);

            var row2cell4 = row2.CreateCell(4);
            row2cell4.CellStyle = styleTitle3;
            row2cell4.SetCellValue("生产日期：");

            var row2cell6 = row2.CreateCell(6);
            row2cell6.CellStyle = styleTitle31;
            row2cell6.SetCellValue(m.CreateDate);

            IRow row3 = sheet1.CreateRow(3);
            var row3cell0 = row3.CreateCell(0);
            row3cell0.CellStyle = styleTitle311;
            row3cell0.SetCellValue("芯片型号：" + m.SourceGoodsModel);

            var row3cell2 = row3.CreateCell(2);
            row3cell2.CellStyle = styleTitle315;
            row3cell2.SetCellValue("管壳型号：" + m.GuanKe);

            var row3cell4 = row3.CreateCell(4);
            row3cell4.CellStyle = styleTitle315;
            row3cell4.SetCellValue("焊料型号：" + m.HanLiao);

            row3.CreateCell(1).CellStyle = styleTitle315;
            row3.CreateCell(3).CellStyle = styleTitle315;
            row3.CreateCell(5).CellStyle = styleTitle315;
            row3.CreateCell(6).CellStyle = styleTitle315;
            row3.CreateCell(7).CellStyle = styleTitle312;

            IRow row31 = sheet1.CreateRow(4);
            var row31cell0 = row31.CreateCell(0);
            row31cell0.CellStyle = styleTitle313;
            row31cell0.SetCellValue("胶体类型：" + m.JiaoTi);

            var row31cell2 = row31.CreateCell(2);
            row31cell2.CellStyle = styleTitle316;
            row31cell2.SetCellValue("内引线：" + m.NeiYinXian);

            var row31cell4 = row31.CreateCell(4);
            row31cell4.CellStyle = styleTitle316;
            row31cell4.SetCellValue("其它：" + m.QiTa);


            row31.CreateCell(1).CellStyle = styleTitle316;
            row31.CreateCell(3).CellStyle = styleTitle316;
            row31.CreateCell(5).CellStyle = styleTitle316;
            row31.CreateCell(6).CellStyle = styleTitle316;
            row31.CreateCell(7).CellStyle = styleTitle314;


            IRow row1 = sheet1.CreateRow(5);

            string[] title1 = new string[] {
               "工序",
               "使用设备编号",
               "条件",
               "工序检验结果",
               "接数（支）",
               "交数（支）",
               "操作者",
               "日/月",
            };

            for (int i = 0; i < 8; i++)
            {
                var cellHeard = row1.CreateCell(i);
                cellHeard.CellStyle = styleHeader;
                cellHeard.SetCellValue(title1[i]);
            }


            for (int x = 0; x < dt.Rows.Count; x++)
            {


                IRow row4 = sheet1.CreateRow(6 + x);
                string[] title4 = new string[] {
               dt.Rows[x]["ProName"].ToString(),
               dt.Rows[x]["SheBei"].ToString(),
               dt.Rows[x]["TiaoJian"].ToString(),
               dt.Rows[x]["CheckRemark"].ToString(),
               dt.Rows[x]["InQty"].ToString(),
               dt.Rows[x]["OutQty"].ToString(),
               dt.Rows[x]["UserName"].ToString(),
               Convert.ToDateTime(dt.Rows[x]["EndDate"].ToString()).ToString("dd/MM")
                };
                for (int i = 0; i < 8; i++)
                {
                    var cellHeard = row4.CreateCell(i);
                    cellHeard.CellStyle = style;
                    cellHeard.SetCellValue(title4[i]);
                }
            }


            sheet1.SetColumnWidth(0, 15 * 256);
            sheet1.SetColumnWidth(1, 12 * 256);
            sheet1.SetColumnWidth(2, 26 * 256);
            sheet1.SetColumnWidth(3, 35 * 256);
            sheet1.SetColumnWidth(4, 12 * 256);
            sheet1.SetColumnWidth(5, 12 * 256);
            sheet1.SetColumnWidth(6, 12 * 256);
            sheet1.SetColumnWidth(7, 12 * 256);


            System.IO.MemoryStream file = new System.IO.MemoryStream();
            hssfworkbook.Write(file);
            file.Seek(0, System.IO.SeekOrigin.Begin);

            return file;

        }


        public static MemoryStream Bill4ToExcel(DataTable dt, Models.MVC.Bill4IndexModel m)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 17));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 2));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 3, 5));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 6, 8));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 9, 11));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 12, 14));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 15, 17));

            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 0, 2));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 3, 5));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 6, 8));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 9, 11));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 12, 14));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 15, 17));

            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 0, 2));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 3, 5));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 6, 8));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 9, 11));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 12, 14));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 15, 17));

            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 5, 0, 0));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 5, 1, 2));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 5, 3, 7));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 5, 8, 8));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 5, 9, 9));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 10, 13));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(5, 5, 10, 11));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(5, 5, 12, 13));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 5, 14, 15));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 5, 16, 16));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 5, 17, 17));


            //给标题头添加格式
            ICellStyle styleTitle = hssfworkbook.CreateCellStyle();
            styleTitle.Alignment = HorizontalAlignment.Center;//单元格水平居中
            IFont fontTitle = hssfworkbook.CreateFont();//新建一个字体样式对象
            //fontTitle.IsBold = true;//设置字体加粗样式
            fontTitle.FontHeightInPoints = 14;//设置字体大小
            styleTitle.SetFont(fontTitle);//使用SetFont方法将字体样式添加到单元格样式中
            ICellStyle styleTitle1 = hssfworkbook.CreateCellStyle();
            styleTitle1.CloneStyleFrom(styleTitle);//克隆公共的样式
            styleTitle1.Alignment = HorizontalAlignment.Right;
            styleTitle1.VerticalAlignment = VerticalAlignment.Center;
            styleTitle.VerticalAlignment = VerticalAlignment.Center;

            styleTitle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            styleTitle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            styleTitle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            styleTitle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

            //公共样式：加边框
            NPOI.SS.UserModel.ICellStyle style = hssfworkbook.CreateCellStyle();
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            //style.Alignment = HorizontalAlignment.Center;
            IFont font = hssfworkbook.CreateFont();
            font.FontHeightInPoints = 12;
            style.SetFont(font);
            style.VerticalAlignment = VerticalAlignment.Center;
            style.Alignment = HorizontalAlignment.Center;//单元格水平居中
            style.WrapText = true;

            //表头样式
            NPOI.SS.UserModel.ICellStyle styleHeader = hssfworkbook.CreateCellStyle();
            styleHeader.CloneStyleFrom(style);//克隆公共的样式
            styleHeader.Alignment = HorizontalAlignment.Center;//单元格水平居中
            IFont fontHeader = hssfworkbook.CreateFont();//新建一个字体样式对象
            fontHeader.IsBold = true;//设置字体加粗样式
            fontHeader.FontHeightInPoints = 12;//设置字体加粗样式
            styleHeader.SetFont(fontHeader);//使用SetFont方法将字体样式添加到单元格样式中
            styleHeader.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            styleHeader.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            styleHeader.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            styleHeader.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            styleHeader.Alignment = HorizontalAlignment.Center;//单元格水平居中
            styleHeader.VerticalAlignment = VerticalAlignment.Center;


            IRow rowTitle = sheet1.CreateRow(0);
            rowTitle.Height = 30 * 20;//设置行高
            var cellTitel = rowTitle.CreateCell(0);
            cellTitel.CellStyle = styleTitle;
            cellTitel.SetCellValue("成品筛选情况报表");




            IRow row1 = sheet1.CreateRow(1);
            row1.Height = 25 * 20;//设置行高
            var row1cell1 = row1.CreateCell(0);
            row1cell1.CellStyle = style;
            row1cell1.SetCellValue("产品型号");

            var row1cell3 = row1.CreateCell(3);
            row1cell3.CellStyle = style;
            row1cell3.SetCellValue(m.GoodsModel);

            var row1cell6 = row1.CreateCell(6);
            row1cell6.CellStyle = style;
            row1cell6.SetCellValue("流程卡号");

            var row1cell9 = row1.CreateCell(9);
            row1cell9.CellStyle = style;
            row1cell9.SetCellValue(m.Code);

            var row1cell12 = row1.CreateCell(12);
            row1cell12.CellStyle = style;
            row1cell12.SetCellValue("封装外形");

            var row1cell15 = row1.CreateCell(15);
            row1cell15.CellStyle = style;
            row1cell15.SetCellValue(m.WaiXing);

            for (int i = 0; i < 18; i++)
            {
                if (i != 0 && i != 3 && i != 9 && i != 12 && i != 6 && i != 15)
                {
                    var rowtemp = row1.CreateCell(i);
                    rowtemp.CellStyle = style;
                    rowtemp.SetCellValue("");
                }
            }



            IRow row2 = sheet1.CreateRow(2);
            row2.Height = 25 * 20;//设置行高
            var row2cell1 = row2.CreateCell(0);
            row2cell1.CellStyle = style;
            row2cell1.SetCellValue("质量等级");

            var row2cell3 = row2.CreateCell(3);
            row2cell3.CellStyle = style;
            row2cell3.SetCellValue(m.DengJi);

            var row2cell6 = row2.CreateCell(6);
            row2cell6.CellStyle = style;
            row2cell6.SetCellValue("执行标准");

            var row2cell9 = row2.CreateCell(9);
            row2cell9.CellStyle = style;
            row2cell9.SetCellValue("");

            var row2cell12 = row2.CreateCell(12);
            row2cell12.CellStyle = style;
            row2cell12.SetCellValue("技术协议");

            var row2cell15 = row2.CreateCell(15);
            row2cell15.CellStyle = style;
            row2cell15.SetCellValue("");
            for (int i = 0; i < 18; i++)
            {
                if (i != 0 && i != 3 && i != 6 && i != 9 && i != 12 && i != 15)
                {
                    var rowtemp = row2.CreateCell(i);
                    rowtemp.CellStyle = style;
                    rowtemp.SetCellValue("");
                }
            }


            IRow row3 = sheet1.CreateRow(3);
            row3.Height = 25 * 20;//设置行高
            var row3cell1 = row3.CreateCell(0);
            row3cell1.CellStyle = style;
            row3cell1.SetCellValue("生产日期");

            var row3cell3 = row3.CreateCell(3);
            row3cell3.CellStyle = style;
            row3cell3.SetCellValue(m.CreateDate);

            var row3cell6 = row3.CreateCell(6);
            row3cell6.CellStyle = style;
            row3cell6.SetCellValue("生产批号");

            var row3cell9 = row3.CreateCell(9);
            row3cell9.CellStyle = style;
            row3cell9.SetCellValue(m.Number);

            var row3cell12 = row3.CreateCell(12);
            row3cell12.CellStyle = style;
            row3cell12.SetCellValue("计划单编号");

            var row3cell15 = row3.CreateCell(15);
            row3cell15.CellStyle = style;
            row3cell15.SetCellValue("");

            for (int i = 0; i < 18; i++)
            {
                if (i != 0 && i != 3 && i != 6 && i != 9 && i != 12 && i != 15)
                {
                    var rowtemp = row3.CreateCell(i);
                    rowtemp.CellStyle = style;
                    rowtemp.SetCellValue("");
                }
            }

            IRow row4 = sheet1.CreateRow(4);
            row4.Height = 25 * 20;//设置行高
            var row4cell0 = row4.CreateCell(0);
            row4cell0.CellStyle = style;
            row4cell0.SetCellValue("序号");

            var row4cell1 = row4.CreateCell(1);
            row4cell1.CellStyle = style;
            row4cell1.SetCellValue("筛选项目");

            var row4cell2 = row4.CreateCell(2);
            row4cell2.CellStyle = style;
            row4cell2.SetCellValue("");

            var row4cell3 = row4.CreateCell(3);
            row4cell3.CellStyle = style;
            row4cell3.SetCellValue("筛选条件");

            var row4cell4 = row4.CreateCell(4);
            row4cell4.CellStyle = style;
            row4cell4.SetCellValue("");

            var row4cell5 = row4.CreateCell(5);
            row4cell5.CellStyle = style;
            row4cell5.SetCellValue("");

            var row4cell6 = row4.CreateCell(6);
            row4cell6.CellStyle = style;
            row4cell6.SetCellValue("");

            var row4cell7 = row4.CreateCell(7);
            row4cell7.CellStyle = style;
            row4cell7.SetCellValue("");

            var row4cell8 = row4.CreateCell(8);
            row4cell8.CellStyle = style;
            row4cell8.SetCellValue("操作者");

            var row4cell9 = row4.CreateCell(9);
            row4cell9.CellStyle = style;
            row4cell9.SetCellValue("日期");

            var row4cell10 = row4.CreateCell(10);
            row4cell10.CellStyle = style;
            row4cell10.SetCellValue("筛选统计");

            var row4cell11 = row4.CreateCell(11);
            row4cell11.CellStyle = style;
            row4cell11.SetCellValue("");

            var row4cell12 = row4.CreateCell(12);
            row4cell12.CellStyle = style;
            row4cell12.SetCellValue("");

            var row4cell13 = row4.CreateCell(13);
            row4cell13.CellStyle = style;
            row4cell13.SetCellValue("");

            var row4cell14 = row4.CreateCell(14);
            row4cell14.CellStyle = style;
            row4cell14.SetCellValue("主要失效模式");

            var row4cell15 = row4.CreateCell(15);
            row4cell15.CellStyle = style;
            row4cell15.SetCellValue("");

            var row4cell16 = row4.CreateCell(16);
            row4cell16.CellStyle = style;
            row4cell16.SetCellValue("测试者");

            var row4cell17 = row4.CreateCell(17);
            row4cell17.CellStyle = style;
            row4cell17.SetCellValue("日期");

            IRow row5 = sheet1.CreateRow(5);
            row5.Height = 25 * 20;//设置行高
            var row5cell10 = row5.CreateCell(10);
            row5cell10.CellStyle = style;
            row5cell10.SetCellValue("筛选总数");

            var row5cell12 = row5.CreateCell(12);
            row5cell12.CellStyle = style;
            row5cell12.SetCellValue("淘汰数");

            for (int i = 0; i < 18; i++)
            {
                if (i != 10 && i != 12)
                {
                    var rowtemp = row5.CreateCell(i);
                    rowtemp.CellStyle = style;
                    rowtemp.SetCellValue("");
                }
            }

            for (int x = 0; x < dt.Rows.Count; x++)
            {
                sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6 + x, 6 + x, 1, 2));
                sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6 + x, 6 + x, 3, 7));
                sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6 + x, 6 + x, 10, 11));
                sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6 + x, 6 + x, 12, 13));
                sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6 + x, 6 + x, 14, 15));


                IRow rowbody = sheet1.CreateRow(6 + x);
                //rowbody.Height = 25 * 20;//设置行高

                var rowbodycell0 = rowbody.CreateCell(0);
                rowbodycell0.CellStyle = style;
                rowbodycell0.SetCellValue(dt.Rows[x]["RowID"].ToString());

                var rowbodycell1 = rowbody.CreateCell(1);
                rowbodycell1.CellStyle = style;
                rowbodycell1.SetCellValue(dt.Rows[x]["ProName"].ToString());

                var rowbodycell3 = rowbody.CreateCell(3);
                rowbodycell3.CellStyle = style;
                rowbodycell3.SetCellValue(dt.Rows[x]["ShaiXuanModel"].ToString());

                var rowbodycell8 = rowbody.CreateCell(8);
                rowbodycell8.CellStyle = style;
                rowbodycell8.SetCellValue(dt.Rows[x]["UserName"].ToString());

                var rowbodycell9 = rowbody.CreateCell(9);
                rowbodycell9.CellStyle = style;
                rowbodycell9.SetCellValue(dt.Rows[x]["BeginDate"].ToString() + "至" + dt.Rows[x]["EndDate"].ToString());

                var rowbodycell10 = rowbody.CreateCell(10);
                rowbodycell10.CellStyle = style;
                rowbodycell10.SetCellValue(dt.Rows[x]["Qty"].ToString());

                var rowbodycell12 = rowbody.CreateCell(12);
                rowbodycell12.CellStyle = style;
                rowbodycell12.SetCellValue(dt.Rows[x]["FailQty"].ToString());

                var rowbodycell14 = rowbody.CreateCell(14);
                rowbodycell14.CellStyle = style;
                rowbodycell14.SetCellValue(dt.Rows[x]["ShiXiao"].ToString());

                var rowbodycell16 = rowbody.CreateCell(16);
                rowbodycell16.CellStyle = style;
                rowbodycell16.SetCellValue(dt.Rows[x]["CheShiUserName"].ToString());

                var rowbodycell17 = rowbody.CreateCell(17);
                rowbodycell17.CellStyle = style;
                rowbodycell17.SetCellValue(dt.Rows[x]["CheShiBeginDate"].ToString() + "至" + dt.Rows[x]["CheShiEndDate"].ToString());

                for (int i = 0; i < 18; i++)
                {
                    if (i == 2 || i == 4 || i == 5 || i == 6 || i == 7 || i == 11 || i == 13 || i == 15)
                    {
                        var rowtemp = rowbody.CreateCell(i);
                        rowtemp.CellStyle = style;
                        rowtemp.SetCellValue("");
                    }
                }


            }

            System.IO.MemoryStream file = new System.IO.MemoryStream();
            hssfworkbook.Write(file);
            file.Seek(0, System.IO.SeekOrigin.Begin);

            return file;

        }

        public static MemoryStream Bill5ToExcel(DataTable dt, Models.MVC.Bill5IndexModel m)
        {
            IWorkbook hssfworkbook = null;
            ISheet sheet1 = null;
            string path = System.AppDomain.CurrentDomain.BaseDirectory.ToString();
            var fileName = Guid.NewGuid().ToString() + ".xls";
            File.Copy(path + "/Files/电源常规产品流程卡.xls", path + "/Files/" + fileName, true);
            FileStream fs = new FileStream(path + "/Files/" + fileName, FileMode.Open, FileAccess.ReadWrite);
            hssfworkbook = new HSSFWorkbook(fs);
            sheet1 = hssfworkbook.GetSheetAt(0);


            sheet1.GetRow(12).GetCell(3).SetCellValue("产品名称：" + m.GoodsName);
            sheet1.GetRow(14).GetCell(3).SetCellValue("产品型号：" + m.GoodsModel);
            sheet1.GetRow(16).GetCell(3).SetCellValue("生产批号：" + m.Number);
            sheet1.GetRow(18).GetCell(3).SetCellValue("生产日期：" + m.CreateDate);
            sheet1.GetRow(20).GetCell(3).SetCellValue("流程卡号：" + m.Code);

            sheet1.GetRow(26).GetCell(17).SetCellValue(m.GoodsModel);
            sheet1.GetRow(26).GetCell(22).SetCellValue(m.Number);
            sheet1.GetRow(27).GetCell(17).SetCellValue(m.Qty.ToString());
            sheet1.GetRow(27).GetCell(22).SetCellValue(m.GoodsNumber);
            sheet1.GetRow(61).GetCell(17).SetCellValue(m.GoodsModel);
            sheet1.GetRow(61).GetCell(22).SetCellValue(m.Number);
            sheet1.GetRow(91).GetCell(17).SetCellValue(m.GoodsModel);
            sheet1.GetRow(91).GetCell(22).SetCellValue(m.Number);
            sheet1.GetRow(119).GetCell(17).SetCellValue(m.GoodsModel);
            sheet1.GetRow(119).GetCell(22).SetCellValue(m.Number);

            foreach (DataRow item in dt.Rows)
            {
                switch (item["ShowIndex"].ToString())
                {
                    case "1":
                        sheet1.GetRow(31).GetCell(15).SetCellValue(Convert.ToDouble(item["InQty"]));
                        sheet1.GetRow(31).GetCell(16).SetCellValue(Convert.ToDouble(item["OutQty"]));
                        sheet1.GetRow(31).GetCell(18).SetCellValue(Convert.ToDouble(item["FailQty"]));
                        sheet1.GetRow(31).GetCell(19).SetCellValue(Convert.ToString(item["UserName"]));
                        sheet1.GetRow(31).GetCell(21).SetCellValue("——");
                        sheet1.GetRow(31).GetCell(23).SetCellValue(Convert.ToString(item["Remark"]));
                        break;
                    case "2":
                        sheet1.GetRow(34).GetCell(15).SetCellValue(Convert.ToDouble(item["InQty"]));
                        sheet1.GetRow(34).GetCell(16).SetCellValue(Convert.ToDouble(item["OutQty"]));
                        sheet1.GetRow(34).GetCell(18).SetCellValue(Convert.ToDouble(item["FailQty"]));
                        sheet1.GetRow(34).GetCell(19).SetCellValue(Convert.ToString(item["UserName"]));
                        sheet1.GetRow(34).GetCell(21).SetCellValue(Convert.ToString(item["CheckUserName"]));
                        sheet1.GetRow(34).GetCell(23).SetCellValue(Convert.ToString(item["Remark"]));
                        break;
                    case "3":
                        sheet1.GetRow(46).GetCell(15).SetCellValue(Convert.ToDouble(item["InQty"]));
                        sheet1.GetRow(46).GetCell(16).SetCellValue(Convert.ToDouble(item["OutQty"]));
                        sheet1.GetRow(46).GetCell(18).SetCellValue(Convert.ToDouble(item["FailQty"]));
                        sheet1.GetRow(46).GetCell(19).SetCellValue(Convert.ToString(item["UserName"]));
                        sheet1.GetRow(46).GetCell(21).SetCellValue("——");
                        sheet1.GetRow(46).GetCell(23).SetCellValue(Convert.ToString(item["Remark"]));
                        break;
                    case "4":
                        sheet1.GetRow(49).GetCell(15).SetCellValue(Convert.ToDouble(item["InQty"]));
                        sheet1.GetRow(49).GetCell(16).SetCellValue(Convert.ToDouble(item["OutQty"]));
                        sheet1.GetRow(49).GetCell(18).SetCellValue(Convert.ToDouble(item["FailQty"]));
                        sheet1.GetRow(49).GetCell(19).SetCellValue("——");
                        sheet1.GetRow(49).GetCell(21).SetCellValue(Convert.ToString(item["CheckUserName"]));
                        sheet1.GetRow(49).GetCell(23).SetCellValue(Convert.ToString(item["Remark"]));
                        break;
                    case "5":
                        sheet1.GetRow(52).GetCell(15).SetCellValue(Convert.ToDouble(item["InQty"]));
                        sheet1.GetRow(52).GetCell(16).SetCellValue(Convert.ToDouble(item["OutQty"]));
                        sheet1.GetRow(52).GetCell(18).SetCellValue(Convert.ToDouble(item["FailQty"]));
                        sheet1.GetRow(52).GetCell(19).SetCellValue(Convert.ToString(item["UserName"]));
                        sheet1.GetRow(52).GetCell(21).SetCellValue(Convert.ToString(item["CheckUserName"]));
                        sheet1.GetRow(52).GetCell(23).SetCellValue(Convert.ToString(item["Remark"]));
                        break;
                    case "6":
                        sheet1.GetRow(70).GetCell(15).SetCellValue(Convert.ToDouble(item["InQty"]));
                        sheet1.GetRow(70).GetCell(16).SetCellValue(Convert.ToDouble(item["OutQty"]));
                        sheet1.GetRow(70).GetCell(18).SetCellValue(Convert.ToDouble(item["FailQty"]));
                        sheet1.GetRow(70).GetCell(19).SetCellValue(Convert.ToString(item["UserName"]));
                        sheet1.GetRow(70).GetCell(21).SetCellValue(Convert.ToString(item["CheckUserName"]));
                        sheet1.GetRow(70).GetCell(23).SetCellValue(Convert.ToString(item["Remark"]));
                        break;
                    case "7":
                        sheet1.GetRow(95).GetCell(15).SetCellValue(Convert.ToDouble(item["InQty"]));
                        sheet1.GetRow(95).GetCell(16).SetCellValue(Convert.ToDouble(item["OutQty"]));
                        sheet1.GetRow(95).GetCell(18).SetCellValue(Convert.ToDouble(item["FailQty"]));
                        sheet1.GetRow(95).GetCell(19).SetCellValue("——");
                        sheet1.GetRow(95).GetCell(21).SetCellValue(Convert.ToString(item["CheckUserName"]));
                        sheet1.GetRow(95).GetCell(23).SetCellValue(Convert.ToString(item["Remark"]));
                        break;
                    case "8":
                        sheet1.GetRow(99).GetCell(15).SetCellValue(Convert.ToDouble(item["InQty"]));
                        sheet1.GetRow(99).GetCell(16).SetCellValue(Convert.ToDouble(item["OutQty"]));
                        sheet1.GetRow(99).GetCell(18).SetCellValue(Convert.ToDouble(item["FailQty"]));
                        sheet1.GetRow(99).GetCell(19).SetCellValue(Convert.ToString(item["UserName"]));
                        sheet1.GetRow(99).GetCell(21).SetCellValue("——");
                        sheet1.GetRow(99).GetCell(23).SetCellValue(Convert.ToString(item["Remark"]));
                        break;
                    case "9":
                        sheet1.GetRow(103).GetCell(15).SetCellValue(Convert.ToDouble(item["InQty"]));
                        sheet1.GetRow(103).GetCell(16).SetCellValue(Convert.ToDouble(item["OutQty"]));
                        sheet1.GetRow(103).GetCell(18).SetCellValue(Convert.ToDouble(item["FailQty"]));
                        sheet1.GetRow(103).GetCell(19).SetCellValue("——");
                        sheet1.GetRow(103).GetCell(21).SetCellValue(Convert.ToString(item["CheckUserName"]));
                        sheet1.GetRow(103).GetCell(23).SetCellValue(Convert.ToString(item["Remark"]));
                        break;
                    case "10":
                        sheet1.GetRow(107).GetCell(15).SetCellValue(Convert.ToDouble(item["InQty"]));
                        sheet1.GetRow(107).GetCell(16).SetCellValue(Convert.ToDouble(item["OutQty"]));
                        sheet1.GetRow(107).GetCell(18).SetCellValue(Convert.ToDouble(item["FailQty"]));
                        sheet1.GetRow(107).GetCell(19).SetCellValue(Convert.ToString(item["UserName"]));
                        sheet1.GetRow(107).GetCell(21).SetCellValue("——");
                        sheet1.GetRow(107).GetCell(23).SetCellValue(Convert.ToString(item["Remark"]));
                        break;
                    case "11":
                        sheet1.GetRow(112).GetCell(15).SetCellValue(Convert.ToDouble(item["InQty"]));
                        sheet1.GetRow(112).GetCell(16).SetCellValue(Convert.ToDouble(item["OutQty"]));
                        sheet1.GetRow(112).GetCell(18).SetCellValue(Convert.ToDouble(item["FailQty"]));
                        sheet1.GetRow(112).GetCell(19).SetCellValue(Convert.ToString(item["UserName"]));
                        sheet1.GetRow(112).GetCell(21).SetCellValue("——");
                        sheet1.GetRow(112).GetCell(23).SetCellValue(Convert.ToString(item["Remark"]));
                        break;
                    case "12":
                        sheet1.GetRow(123).GetCell(15).SetCellValue(Convert.ToDouble(item["InQty"]));
                        sheet1.GetRow(123).GetCell(16).SetCellValue(Convert.ToDouble(item["OutQty"]));
                        sheet1.GetRow(123).GetCell(18).SetCellValue(Convert.ToDouble(item["FailQty"]));
                        sheet1.GetRow(123).GetCell(19).SetCellValue("——");
                        sheet1.GetRow(123).GetCell(21).SetCellValue(Convert.ToString(item["CheckUserName"]));
                        sheet1.GetRow(123).GetCell(23).SetCellValue(Convert.ToString(item["Remark"]));
                        break;
                    case "13":
                        sheet1.GetRow(127).GetCell(15).SetCellValue(Convert.ToDouble(item["InQty"]));
                        sheet1.GetRow(127).GetCell(16).SetCellValue(Convert.ToDouble(item["OutQty"]));
                        sheet1.GetRow(127).GetCell(18).SetCellValue(Convert.ToDouble(item["FailQty"]));
                        sheet1.GetRow(127).GetCell(19).SetCellValue(Convert.ToString(item["UserName"]));
                        sheet1.GetRow(127).GetCell(21).SetCellValue("——");
                        sheet1.GetRow(127).GetCell(23).SetCellValue(Convert.ToString(item["Remark"]));
                        break;
                    case "14":
                        sheet1.GetRow(131).GetCell(15).SetCellValue(Convert.ToDouble(item["InQty"]));
                        sheet1.GetRow(131).GetCell(16).SetCellValue(Convert.ToDouble(item["OutQty"]));
                        sheet1.GetRow(131).GetCell(18).SetCellValue(Convert.ToDouble(item["FailQty"]));
                        sheet1.GetRow(131).GetCell(19).SetCellValue(Convert.ToString(item["UserName"]));
                        sheet1.GetRow(131).GetCell(21).SetCellValue("——");
                        sheet1.GetRow(131).GetCell(23).SetCellValue(Convert.ToString(item["Remark"]));
                        break;
                    case "15":
                        sheet1.GetRow(136).GetCell(15).SetCellValue(Convert.ToDouble(item["InQty"]));
                        sheet1.GetRow(136).GetCell(16).SetCellValue(Convert.ToDouble(item["OutQty"]));
                        sheet1.GetRow(136).GetCell(18).SetCellValue(Convert.ToDouble(item["FailQty"]));
                        sheet1.GetRow(136).GetCell(19).SetCellValue("——");
                        sheet1.GetRow(136).GetCell(21).SetCellValue(Convert.ToString(item["CheckUserName"]));
                        sheet1.GetRow(136).GetCell(23).SetCellValue(Convert.ToString(item["Remark"]));
                        break;
                    case "16":
                        sheet1.GetRow(140).GetCell(15).SetCellValue(Convert.ToDouble(item["InQty"]));
                        sheet1.GetRow(140).GetCell(16).SetCellValue(Convert.ToDouble(item["OutQty"]));
                        sheet1.GetRow(140).GetCell(18).SetCellValue(Convert.ToDouble(item["FailQty"]));
                        sheet1.GetRow(140).GetCell(19).SetCellValue("——");
                        sheet1.GetRow(140).GetCell(21).SetCellValue(Convert.ToString(item["CheckUserName"]));
                        sheet1.GetRow(140).GetCell(23).SetCellValue(Convert.ToString(item["Remark"]));
                        break;
                    case "17":
                        sheet1.GetRow(144).GetCell(15).SetCellValue(Convert.ToDouble(item["InQty"]));
                        sheet1.GetRow(144).GetCell(16).SetCellValue(Convert.ToDouble(item["OutQty"]));
                        sheet1.GetRow(144).GetCell(18).SetCellValue(Convert.ToDouble(item["FailQty"]));
                        sheet1.GetRow(144).GetCell(19).SetCellValue(Convert.ToString(item["UserName"]));
                        sheet1.GetRow(144).GetCell(21).SetCellValue("——");
                        sheet1.GetRow(144).GetCell(23).SetCellValue(Convert.ToString(item["Remark"]));
                        break;
                    default:
                        break;
                }
            }
            System.IO.MemoryStream file = new System.IO.MemoryStream();
            hssfworkbook.Write(file);
            file.Seek(0, System.IO.SeekOrigin.Begin);
            File.Delete(path + "/Files/" + fileName);

            return file;

        }

        public static MemoryStream Bill6ToExcel(DataTable dt, Models.MVC.Bill6IndexModel m)
        {
            IWorkbook hssfworkbook = null;
            ISheet sheet1 = null;
            string path = System.AppDomain.CurrentDomain.BaseDirectory.ToString();
            var fileName = Guid.NewGuid().ToString() + ".xls";
            File.Copy(path + "/Files/生产作业计划.xls", path + "/Files/"+fileName, true);
            FileStream fs = new FileStream(path + "/Files/"+fileName, FileMode.Open, FileAccess.ReadWrite);
            hssfworkbook = new HSSFWorkbook(fs);
            sheet1 = hssfworkbook.GetSheetAt(0);


            sheet1.GetRow(1).Cells[0].SetCellValue("计划号：" + m.JiHuaHao);

            sheet1.GetRow(14).Cells[0].SetCellValue(m.HeTongCode);
            sheet1.GetRow(14).Cells[1].SetCellValue(m.OrderDate);
            sheet1.GetRow(14).Cells[2].SetCellValue(m.XuFangDaiHao);
            sheet1.GetRow(14).Cells[4].SetCellValue(m.GoodsName);
            sheet1.GetRow(14).Cells[5].SetCellValue(m.GoodsModel);
            sheet1.GetRow(14).Cells[7].SetCellValue(m.DengJi);
            sheet1.GetRow(14).Cells[9].SetCellValue(m.WaiXing);
            sheet1.GetRow(14).Cells[10].SetCellValue(Convert.ToDouble(m.HeTongCount));
            sheet1.GetRow(14).Cells[12].SetCellValue(Convert.ToDouble(m.JiHuaQty));
            sheet1.GetRow(14).Cells[13].SetCellValue(m.JiaoFuDate);

            sheet1.GetRow(18).Cells[0].SetCellValue(m.GuiFanHao);
            sheet1.GetRow(18).Cells[3].SetCellValue(m.WenJianBianHao);
            sheet1.GetRow(18).Cells[6].SetCellValue(m.JianZhiYanShou);
            sheet1.GetRow(18).Cells[10].SetCellValue(m.QiTa);

            int i = 0;
            foreach (DataRow item in dt.Rows)
            {
                sheet1.GetRow(4 + i).Cells[0].SetCellValue(item["GoodsName"].ToString());
                sheet1.GetRow(4 + i).Cells[1].SetCellValue(item["GoodsModel"].ToString());
                sheet1.GetRow(4 + i).Cells[3].SetCellValue(item["HuoYuanHao"].ToString());
                sheet1.GetRow(4 + i).Cells[5].SetCellValue(item["JianYanPiHao"].ToString());
                sheet1.GetRow(4 + i).Cells[8].SetCellValue(item["ZhiZhaoChangShang"].ToString());
                sheet1.GetRow(4 + i).Cells[11].SetCellValue(item["LingQty"].ToString());
                i++;
            }
            System.IO.MemoryStream file = new System.IO.MemoryStream();
            hssfworkbook.Write(file);
            file.Seek(0, System.IO.SeekOrigin.Begin);
            File.Delete(path+ "/Files/" + fileName);
            return file;

        }

        public static MemoryStream Bill6ToExcel2(DataTable bomdt, DataTable dt, Models.MVC.Bill6IndexModel m)
        {
            IWorkbook hssfworkbook = null;
            ISheet sheet1 = null;
            string path = System.AppDomain.CurrentDomain.BaseDirectory.ToString();
            var fileName = Guid.NewGuid().ToString() + ".xls";
            File.Copy(path + "/Files/集成电路封装流程卡.xls", path + "/Files/" + fileName, true);
            FileStream fs = new FileStream(path + "/Files/" + fileName, FileMode.Open, FileAccess.ReadWrite);
            hssfworkbook = new HSSFWorkbook(fs);
            sheet1 = hssfworkbook.GetSheetAt(0);

            IFont font = hssfworkbook.CreateFont();
            font.FontHeightInPoints = 12;
            NPOI.SS.UserModel.ICellStyle style = hssfworkbook.CreateCellStyle();
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            //style.Alignment = HorizontalAlignment.Center;
            style.SetFont(font);
            style.VerticalAlignment = VerticalAlignment.Center;
            style.Alignment = HorizontalAlignment.Center;//单元格水平居中
            style.WrapText = true;



            sheet1.GetRow(1).Cells[0].SetCellValue("产品型号：" + m.GoodsModel + "　　质量等级：" + m.DengJi + "级　　　投料数量：" + m.Qty + "　　执行标准：" + m.BiaoZhun + "　　　生产计划编号：" + m.JiHuaHao + "　　　生产批号：" + m.Number);

            int i = 0;
            foreach (DataRow item in bomdt.Rows)
            {
                if (i < 4)
                {
                    sheet1.GetRow(4 + i).Cells[0].SetCellValue(item["GoodsName"].ToString());
                    sheet1.GetRow(4 + i).Cells[1].SetCellValue(item["GoodsModel"].ToString());
                    sheet1.GetRow(4 + i).Cells[3].SetCellValue(item["HuoYuanHao"].ToString());
                    sheet1.GetRow(4 + i).Cells[4].SetCellValue(item["JianYanPiHao"].ToString());
                    sheet1.GetRow(4 + i).Cells[6].SetCellValue(item["ZhiZhaoChangShang"].ToString());
                }
                else
                {
                    sheet1.GetRow(4 + i - 4).Cells[12].SetCellValue(item["GoodsName"].ToString());
                    sheet1.GetRow(4 + i - 4).Cells[13].SetCellValue(item["GoodsModel"].ToString());
                    sheet1.GetRow(4 + i - 4).Cells[15].SetCellValue(item["HuoYuanHao"].ToString());
                    sheet1.GetRow(4 + i - 4).Cells[16].SetCellValue(item["JianYanPiHao"].ToString());
                    sheet1.GetRow(4 + i - 4).Cells[17].SetCellValue(item["ZhiZhaoChangShang"].ToString());
                }
                i++;
            }
            i = 0;
            foreach (DataRow item in dt.Rows)
            {
                IRow row = sheet1.CreateRow(11 + i);
                sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(11 + i, 11 + i, 0, 1));
                sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(11 + i, 11 + i, 3, 5));
                sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(11 + i, 11 + i, 6, 7));
                sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(11 + i, 11 + i, 9, 10));
                sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(11 + i, 11 + i, 11, 12));
                sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(11 + i, 11 + i, 14, 15));
                row.CreateCell(0).SetCellValue(item["ProName"].ToString());
                row.CreateCell(1);
                row.CreateCell(2).SetCellValue(item["SheBei"].ToString());
                row.CreateCell(3).SetCellValue(item["GongYi"].ToString());
                row.CreateCell(4);
                row.CreateCell(5);
                row.CreateCell(6).SetCellValue(item["InQty"].ToString());
                row.CreateCell(7);
                row.CreateCell(8).SetCellValue("");
                row.CreateCell(9).SetCellValue(item["OutQty"].ToString());
                row.CreateCell(10);
                row.CreateCell(11).SetCellValue("");
                row.CreateCell(12);
                row.CreateCell(13).SetCellValue(item["FailQty"].ToString());
                row.CreateCell(14).SetCellValue(item["CheckRemark"].ToString());
                row.CreateCell(15);
                row.CreateCell(16).SetCellValue(item["UserName"].ToString());
                row.CreateCell(17).SetCellValue(Convert.ToDateTime(item["EndDate"]).ToString("yyyy-MM-dd"));

                for (int j = 0; j < 18; j++)
                {
                    row.Cells[j].CellStyle = style;
                }
                row.Height = 20 * 60;
                i++;
            }
            System.IO.MemoryStream file = new System.IO.MemoryStream();
            hssfworkbook.Write(file);
            file.Seek(0, System.IO.SeekOrigin.Begin);
            File.Delete(path + "/Files/" + fileName);

            return file;

        }
        public static MemoryStream Bill7ToExcelBak(DataTable dt, Models.MVC.Bill7IndexModel m)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 17));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 2));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 3, 8));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 9, 11));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 12, 17));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 0, 2));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 3, 5));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 6, 8));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 9, 11));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 12, 14));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 15, 17));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 0, 2));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 3, 5));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 6, 8));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 9, 11));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 12, 14));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 15, 17));

            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 5, 0, 0));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 5, 1, 2));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 5, 3, 7));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 5, 8, 8));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 5, 9, 9));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 10, 13));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(5, 5, 10, 11));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(5, 5, 12, 13));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 5, 14, 15));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 5, 16, 16));
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 5, 17, 17));


            //给标题头添加格式
            ICellStyle styleTitle = hssfworkbook.CreateCellStyle();
            styleTitle.Alignment = HorizontalAlignment.Center;//单元格水平居中
            IFont fontTitle = hssfworkbook.CreateFont();//新建一个字体样式对象
            //fontTitle.IsBold = true;//设置字体加粗样式
            fontTitle.FontHeightInPoints = 14;//设置字体大小
            styleTitle.SetFont(fontTitle);//使用SetFont方法将字体样式添加到单元格样式中
            ICellStyle styleTitle1 = hssfworkbook.CreateCellStyle();
            styleTitle1.CloneStyleFrom(styleTitle);//克隆公共的样式
            styleTitle1.Alignment = HorizontalAlignment.Right;
            styleTitle1.VerticalAlignment = VerticalAlignment.Center;
            styleTitle.VerticalAlignment = VerticalAlignment.Center;

            styleTitle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            styleTitle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            styleTitle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            styleTitle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

            //公共样式：加边框
            NPOI.SS.UserModel.ICellStyle style = hssfworkbook.CreateCellStyle();
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            //style.Alignment = HorizontalAlignment.Center;
            IFont font = hssfworkbook.CreateFont();
            font.FontHeightInPoints = 12;
            style.SetFont(font);
            style.VerticalAlignment = VerticalAlignment.Center;
            style.Alignment = HorizontalAlignment.Center;//单元格水平居中
            style.WrapText = true;

            //表头样式
            NPOI.SS.UserModel.ICellStyle styleHeader = hssfworkbook.CreateCellStyle();
            styleHeader.CloneStyleFrom(style);//克隆公共的样式
            styleHeader.Alignment = HorizontalAlignment.Center;//单元格水平居中
            IFont fontHeader = hssfworkbook.CreateFont();//新建一个字体样式对象
            fontHeader.IsBold = true;//设置字体加粗样式
            fontHeader.FontHeightInPoints = 12;//设置字体加粗样式
            styleHeader.SetFont(fontHeader);//使用SetFont方法将字体样式添加到单元格样式中
            styleHeader.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            styleHeader.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            styleHeader.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            styleHeader.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            styleHeader.Alignment = HorizontalAlignment.Center;//单元格水平居中
            styleHeader.VerticalAlignment = VerticalAlignment.Center;


            IRow rowTitle = sheet1.CreateRow(0);
            rowTitle.Height = 30 * 20;//设置行高
            var cellTitel = rowTitle.CreateCell(0);
            cellTitel.CellStyle = styleTitle;
            cellTitel.SetCellValue("集成电路筛选情况报表");




            IRow row1 = sheet1.CreateRow(1);
            row1.Height = 25 * 20;//设置行高
            var row1cell1 = row1.CreateCell(0);
            row1cell1.CellStyle = style;
            row1cell1.SetCellValue("产品型号");

            var row1cell3 = row1.CreateCell(3);
            row1cell3.CellStyle = style;
            row1cell3.SetCellValue(m.GoodsModel);

            var row1cell9 = row1.CreateCell(9);
            row1cell9.CellStyle = style;
            row1cell9.SetCellValue("流程卡号");

            var row1cell12 = row1.CreateCell(12);
            row1cell12.CellStyle = style;
            row1cell12.SetCellValue(m.Code);

            for (int i = 0; i < 18; i++)
            {
                if (i != 0 && i != 3 && i != 9 && i != 12)
                {
                    var rowtemp = row1.CreateCell(i);
                    rowtemp.CellStyle = style;
                    rowtemp.SetCellValue("");
                }
            }



            IRow row2 = sheet1.CreateRow(2);
            row1.Height = 25 * 20;//设置行高
            var row2cell1 = row2.CreateCell(0);
            row2cell1.CellStyle = style;
            row2cell1.SetCellValue("质量等级");

            var row2cell3 = row2.CreateCell(3);
            row2cell3.CellStyle = style;
            row2cell3.SetCellValue(m.DengJi);

            var row2cell6 = row2.CreateCell(6);
            row2cell6.CellStyle = style;
            row2cell6.SetCellValue("执行标准");

            var row2cell9 = row2.CreateCell(9);
            row2cell9.CellStyle = style;
            row2cell9.SetCellValue("");

            var row2cell12 = row2.CreateCell(12);
            row2cell12.CellStyle = style;
            row2cell12.SetCellValue("技术协议");

            var row2cell15 = row2.CreateCell(15);
            row2cell15.CellStyle = style;
            row2cell15.SetCellValue("");
            for (int i = 0; i < 18; i++)
            {
                if (i != 0 && i != 3 && i != 6 && i != 9 && i != 12 && i != 15)
                {
                    var rowtemp = row2.CreateCell(i);
                    rowtemp.CellStyle = style;
                    rowtemp.SetCellValue("");
                }
            }


            IRow row3 = sheet1.CreateRow(3);
            row3.Height = 25 * 20;//设置行高
            var row3cell1 = row3.CreateCell(0);
            row3cell1.CellStyle = style;
            row3cell1.SetCellValue("生产日期");

            var row3cell3 = row3.CreateCell(3);
            row3cell3.CellStyle = style;
            row3cell3.SetCellValue(m.CreateDate);

            var row3cell6 = row3.CreateCell(6);
            row3cell6.CellStyle = style;
            row3cell6.SetCellValue("生产批号");

            var row3cell9 = row3.CreateCell(9);
            row3cell9.CellStyle = style;
            row3cell9.SetCellValue(m.Number);

            var row3cell12 = row3.CreateCell(12);
            row3cell12.CellStyle = style;
            row3cell12.SetCellValue("计划单编号");

            var row3cell15 = row3.CreateCell(15);
            row3cell15.CellStyle = style;
            row3cell15.SetCellValue("");

            for (int i = 0; i < 18; i++)
            {
                if (i != 0 && i != 3 && i != 6 && i != 9 && i != 12 && i != 15)
                {
                    var rowtemp = row3.CreateCell(i);
                    rowtemp.CellStyle = style;
                    rowtemp.SetCellValue("");
                }
            }

            IRow row4 = sheet1.CreateRow(4);
            row4.Height = 25 * 20;//设置行高
            var row4cell0 = row4.CreateCell(0);
            row4cell0.CellStyle = style;
            row4cell0.SetCellValue("序号");

            var row4cell1 = row4.CreateCell(1);
            row4cell1.CellStyle = style;
            row4cell1.SetCellValue("筛选项目");

            var row4cell2 = row4.CreateCell(2);
            row4cell2.CellStyle = style;
            row4cell2.SetCellValue("");

            var row4cell3 = row4.CreateCell(3);
            row4cell3.CellStyle = style;
            row4cell3.SetCellValue("筛选条件");

            var row4cell4 = row4.CreateCell(4);
            row4cell4.CellStyle = style;
            row4cell4.SetCellValue("");

            var row4cell5 = row4.CreateCell(5);
            row4cell5.CellStyle = style;
            row4cell5.SetCellValue("");

            var row4cell6 = row4.CreateCell(6);
            row4cell6.CellStyle = style;
            row4cell6.SetCellValue("");

            var row4cell7 = row4.CreateCell(7);
            row4cell7.CellStyle = style;
            row4cell7.SetCellValue("");

            var row4cell8 = row4.CreateCell(8);
            row4cell8.CellStyle = style;
            row4cell8.SetCellValue("操作者");

            var row4cell9 = row4.CreateCell(9);
            row4cell9.CellStyle = style;
            row4cell9.SetCellValue("日期");

            var row4cell10 = row4.CreateCell(10);
            row4cell10.CellStyle = style;
            row4cell10.SetCellValue("筛选统计");

            var row4cell11 = row4.CreateCell(11);
            row4cell11.CellStyle = style;
            row4cell11.SetCellValue("");

            var row4cell12 = row4.CreateCell(12);
            row4cell12.CellStyle = style;
            row4cell12.SetCellValue("");

            var row4cell13 = row4.CreateCell(13);
            row4cell13.CellStyle = style;
            row4cell13.SetCellValue("");

            var row4cell14 = row4.CreateCell(14);
            row4cell14.CellStyle = style;
            row4cell14.SetCellValue("主要失效模式");

            var row4cell15 = row4.CreateCell(15);
            row4cell15.CellStyle = style;
            row4cell15.SetCellValue("");

            var row4cell16 = row4.CreateCell(16);
            row4cell16.CellStyle = style;
            row4cell16.SetCellValue("测试者");

            var row4cell17 = row4.CreateCell(17);
            row4cell17.CellStyle = style;
            row4cell17.SetCellValue("日期");

            IRow row5 = sheet1.CreateRow(5);
            row5.Height = 25 * 20;//设置行高
            var row5cell10 = row5.CreateCell(10);
            row5cell10.CellStyle = style;
            row5cell10.SetCellValue("筛选总数");

            var row5cell12 = row5.CreateCell(12);
            row5cell12.CellStyle = style;
            row5cell12.SetCellValue("淘汰数");

            for (int i = 0; i < 18; i++)
            {
                if (i != 10 && i != 12)
                {
                    var rowtemp = row5.CreateCell(i);
                    rowtemp.CellStyle = style;
                    rowtemp.SetCellValue("");
                }
            }

            for (int x = 0; x < dt.Rows.Count; x++)
            {
                sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6 + x, 6 + x, 1, 2));
                sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6 + x, 6 + x, 3, 7));
                sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6 + x, 6 + x, 10, 11));
                sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6 + x, 6 + x, 12, 13));
                sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6 + x, 6 + x, 14, 15));


                IRow rowbody = sheet1.CreateRow(6 + x);
                //rowbody.Height = 25 * 20;//设置行高

                var rowbodycell0 = rowbody.CreateCell(0);
                rowbodycell0.CellStyle = style;
                rowbodycell0.SetCellValue(dt.Rows[x]["RowID"].ToString());

                var rowbodycell1 = rowbody.CreateCell(1);
                rowbodycell1.CellStyle = style;
                rowbodycell1.SetCellValue(dt.Rows[x]["ProName"].ToString());

                var rowbodycell3 = rowbody.CreateCell(3);
                rowbodycell3.CellStyle = style;
                rowbodycell3.SetCellValue(dt.Rows[x]["ShaiXuanModel"].ToString());

                var rowbodycell8 = rowbody.CreateCell(8);
                rowbodycell8.CellStyle = style;
                rowbodycell8.SetCellValue(dt.Rows[x]["UserName"].ToString());

                var rowbodycell9 = rowbody.CreateCell(9);
                rowbodycell9.CellStyle = style;
                rowbodycell9.SetCellValue(dt.Rows[x]["CreateDate"].ToString());

                var rowbodycell10 = rowbody.CreateCell(10);
                rowbodycell10.CellStyle = style;
                rowbodycell10.SetCellValue(dt.Rows[x]["Qty"].ToString());

                var rowbodycell12 = rowbody.CreateCell(12);
                rowbodycell12.CellStyle = style;
                rowbodycell12.SetCellValue(dt.Rows[x]["FailQty"].ToString());

                var rowbodycell14 = rowbody.CreateCell(14);
                rowbodycell14.CellStyle = style;
                rowbodycell14.SetCellValue(dt.Rows[x]["ShiXiao"].ToString());

                var rowbodycell16 = rowbody.CreateCell(16);
                rowbodycell16.CellStyle = style;
                rowbodycell16.SetCellValue(dt.Rows[x]["CheShiUserName"].ToString());

                var rowbodycell17 = rowbody.CreateCell(17);
                rowbodycell17.CellStyle = style;
                rowbodycell17.SetCellValue(dt.Rows[x]["CheckDate"].ToString());

                for (int i = 0; i < 18; i++)
                {
                    if (i == 2 || i == 4 || i == 5 || i == 6 || i == 7 || i == 11 || i == 13 || i == 15)
                    {
                        var rowtemp = rowbody.CreateCell(i);
                        rowtemp.CellStyle = style;
                        rowtemp.SetCellValue("");
                    }
                }


            }

            System.IO.MemoryStream file = new System.IO.MemoryStream();
            hssfworkbook.Write(file);
            file.Seek(0, System.IO.SeekOrigin.Begin);

            return file;

        }
        public static MemoryStream Bill7ToExcel(DataTable dt, Models.MVC.Bill7IndexModel m)
        {
            IWorkbook hssfworkbook = null;
            ISheet sheet1 = null;
            string path = System.AppDomain.CurrentDomain.BaseDirectory.ToString();
            var fileName = Guid.NewGuid().ToString() + ".xls";
            File.Copy(path + "/Files/集成电路筛选前部流程卡.xls", path + "/Files/" + fileName, true);
            FileStream fs = new FileStream(path + "/Files/" + fileName, FileMode.Open, FileAccess.ReadWrite);
            hssfworkbook = new HSSFWorkbook(fs);
            sheet1 = hssfworkbook.GetSheetAt(0);

            IFont font = hssfworkbook.CreateFont();
            font.FontHeightInPoints = 12;
            NPOI.SS.UserModel.ICellStyle style = hssfworkbook.CreateCellStyle();
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            //style.Alignment = HorizontalAlignment.Center;
            style.SetFont(font);
            style.VerticalAlignment = VerticalAlignment.Center;
            style.Alignment = HorizontalAlignment.Center;//单元格水平居中
            style.WrapText = true;



            sheet1.GetRow(1).GetCell(2).SetCellValue(m.GoodsModel);
            sheet1.GetRow(1).GetCell(5).SetCellValue(m.DengJi);
            sheet1.GetRow(1).GetCell(10).SetCellValue(m.Code);
            //sheet1.GetRow(2).GetCell(2).SetCellValue(m.SourceGoodsModel);
            //sheet1.GetRow(2).GetCell(5).SetCellValue(m.SourceDengJi);
            //sheet1.GetRow(2).GetCell(10).SetCellValue(m.SourceCode);
            //sheet1.GetRow(3).GetCell(2).SetCellValue(m.KeHuCode);
            //sheet1.GetRow(3).GetCell(5).SetCellValue(m.HeTongHao);
            //sheet1.GetRow(3).GetCell(10).SetCellValue(m.Number);
            sheet1.GetRow(2).GetCell(2).SetCellValue(m.Number);
            sheet1.GetRow(2).GetCell(5).SetCellValue(m.WaiXing);

            int startrow = 4;
            int i = 0;
            foreach (DataRow item in dt.Rows)
            {
                IRow row = sheet1.CreateRow(startrow + i);
                sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(startrow + i, startrow + i, 1, 2));
                sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(startrow + i, startrow + i, 3, 4));
                row.CreateCell(0).SetCellValue(item["RowID"].ToString());
                row.CreateCell(1).SetCellValue(item["ProName"].ToString());
                row.CreateCell(2);
                row.CreateCell(3).SetCellValue(item["ShaiXuanModel"].ToString());
                row.CreateCell(4);
                row.CreateCell(5).SetCellValue(item["InQty"].ToString());
                row.CreateCell(6).SetCellValue(item["OutQty"].ToString());
                row.CreateCell(7).SetCellValue(item["FailQty"].ToString());
                row.CreateCell(8).SetCellValue(item["UserName"].ToString());
                row.CreateCell(9).SetCellValue(item["BeginDate"].ToString());
                row.CreateCell(10).SetCellValue(item["EndDate"].ToString());
                row.CreateCell(11).SetCellValue(item["Remark"].ToString());

                for (int j = 0; j < 12; j++)
                {
                    row.Cells[j].CellStyle = style;
                }
                row.Height = 20 * 25;
                i++;
            }
            System.IO.MemoryStream file = new System.IO.MemoryStream();
            hssfworkbook.Write(file);
            file.Seek(0, System.IO.SeekOrigin.Begin);
            File.Delete(path + "/Files/" + fileName);

            return file;

        }

        public static MemoryStream Bill8ToExcel(DataTable dt, Models.MVC.Bill8IndexModel m)
        {
            IWorkbook hssfworkbook = null;
            ISheet sheet1 = null;
            string path = System.AppDomain.CurrentDomain.BaseDirectory.ToString();
            var fileName = Guid.NewGuid().ToString() + ".xls";
            File.Copy(path + "/Files/集成电路筛选后部流程卡.xls", path + "/Files/" + fileName, true);
            FileStream fs = new FileStream(path + "/Files/" + fileName, FileMode.Open, FileAccess.ReadWrite);
            hssfworkbook = new HSSFWorkbook(fs);
            sheet1 = hssfworkbook.GetSheetAt(0);

            IFont font = hssfworkbook.CreateFont();
            font.FontHeightInPoints = 12;
            NPOI.SS.UserModel.ICellStyle style = hssfworkbook.CreateCellStyle();
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            //style.Alignment = HorizontalAlignment.Center;
            style.SetFont(font);
            style.VerticalAlignment = VerticalAlignment.Center;
            style.Alignment = HorizontalAlignment.Center;//单元格水平居中
            style.WrapText = true;



            sheet1.GetRow(1).GetCell(2).SetCellValue(m.GoodsModel);
            sheet1.GetRow(1).GetCell(5).SetCellValue(m.DengJi);
            sheet1.GetRow(1).GetCell(10).SetCellValue(m.Code);
            sheet1.GetRow(2).GetCell(2).SetCellValue(m.SourceGoodsModel);
            sheet1.GetRow(2).GetCell(5).SetCellValue(m.SourceDengJi);
            sheet1.GetRow(2).GetCell(10).SetCellValue(m.SourceCode);
            sheet1.GetRow(3).GetCell(2).SetCellValue(m.KeHuCode);
            sheet1.GetRow(3).GetCell(5).SetCellValue(m.HeTongHao);
            sheet1.GetRow(3).GetCell(10).SetCellValue(m.Number);
            sheet1.GetRow(4).GetCell(2).SetCellValue(m.NewName);
            sheet1.GetRow(4).GetCell(5).SetCellValue(m.WaiXing);
            sheet1.GetRow(4).GetCell(10).SetCellValue(m.SourceWaiXing);

            int startrow = 6;
            int i = 0;
            foreach (DataRow item in dt.Rows)
            {
                IRow row = sheet1.CreateRow(startrow + i);
                sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(startrow + i, startrow + i, 1, 2));
                sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(startrow + i, startrow + i, 3, 4));
                row.CreateCell(0).SetCellValue(item["RowID"].ToString());
                row.CreateCell(1).SetCellValue(item["ProName"].ToString());
                row.CreateCell(2);
                row.CreateCell(3).SetCellValue(item["ShaiXuanModel"].ToString());
                row.CreateCell(4);
                row.CreateCell(5).SetCellValue(item["InQty"].ToString());
                row.CreateCell(6).SetCellValue(item["OutQty"].ToString());
                row.CreateCell(7).SetCellValue(item["FailQty"].ToString());
                row.CreateCell(8).SetCellValue(item["UserName"].ToString());
                row.CreateCell(9).SetCellValue(item["BeginDate"].ToString());
                row.CreateCell(10).SetCellValue(item["EndDate"].ToString());
                row.CreateCell(11).SetCellValue(item["Remark"].ToString());

                for (int j = 0; j < 12; j++)
                {
                    row.Cells[j].CellStyle = style;
                }
                row.Height = 20 * 25;
                i++;
            }
            System.IO.MemoryStream file = new System.IO.MemoryStream();
            hssfworkbook.Write(file);
            file.Seek(0, System.IO.SeekOrigin.Begin);
            File.Delete(path + "/Files/" + fileName);

            return file;

        }
        public static MemoryStream Bill9ToExcel(DataTable dt, Models.MVC.Bill9IndexModel m)
        {
            IWorkbook hssfworkbook = null;
            ISheet sheet1 = null;
            string path = System.AppDomain.CurrentDomain.BaseDirectory.ToString();
            var fileName = Guid.NewGuid().ToString() + ".xls";
            File.Copy(path + "/Files/集成电路筛选后部流程卡.xls", path + "/Files/" + fileName, true);
            FileStream fs = new FileStream(path + "/Files/" + fileName, FileMode.Open, FileAccess.ReadWrite);
            hssfworkbook = new HSSFWorkbook(fs);
            sheet1 = hssfworkbook.GetSheetAt(0);

            IFont font = hssfworkbook.CreateFont();
            font.FontHeightInPoints = 12;
            NPOI.SS.UserModel.ICellStyle style = hssfworkbook.CreateCellStyle();
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            //style.Alignment = HorizontalAlignment.Center;
            style.SetFont(font);
            style.VerticalAlignment = VerticalAlignment.Center;
            style.Alignment = HorizontalAlignment.Center;//单元格水平居中
            style.WrapText = true;



            sheet1.GetRow(1).GetCell(2).SetCellValue(m.GoodsModel);
            sheet1.GetRow(1).GetCell(5).SetCellValue(m.DengJi);
            sheet1.GetRow(1).GetCell(10).SetCellValue(m.Code);
            sheet1.GetRow(2).GetCell(2).SetCellValue(m.SourceGoodsModel);
            sheet1.GetRow(2).GetCell(5).SetCellValue(m.SourceDengJi);
            sheet1.GetRow(2).GetCell(10).SetCellValue(m.SourceCode);
            sheet1.GetRow(3).GetCell(2).SetCellValue(m.KeHuCode);
            sheet1.GetRow(3).GetCell(5).SetCellValue(m.HeTongHao);
            sheet1.GetRow(3).GetCell(10).SetCellValue(m.Number);
            sheet1.GetRow(4).GetCell(2).SetCellValue(m.NewName);
            sheet1.GetRow(4).GetCell(5).SetCellValue(m.WaiXing);
            sheet1.GetRow(4).GetCell(10).SetCellValue(m.SourceWaiXing);

            int startrow = 6;
            int i = 0;
            foreach (DataRow item in dt.Rows)
            {
                IRow row = sheet1.CreateRow(startrow + i);
                sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(startrow + i, startrow + i, 1, 2));
                sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(startrow + i, startrow + i, 3, 4));
                row.CreateCell(0).SetCellValue(item["RowID"].ToString());
                row.CreateCell(1).SetCellValue(item["ProName"].ToString());
                row.CreateCell(2);
                row.CreateCell(3).SetCellValue(item["ShaiXuanModel"].ToString());
                row.CreateCell(4);
                row.CreateCell(5).SetCellValue(item["InQty"].ToString());
                row.CreateCell(6).SetCellValue(item["OutQty"].ToString());
                row.CreateCell(7).SetCellValue(item["FailQty"].ToString());
                row.CreateCell(8).SetCellValue(item["UserName"].ToString());
                row.CreateCell(9).SetCellValue(item["BeginDate"].ToString());
                row.CreateCell(10).SetCellValue(item["EndDate"].ToString());
                row.CreateCell(11).SetCellValue(item["Remark"].ToString());

                for (int j = 0; j < 12; j++)
                {
                    row.Cells[j].CellStyle = style;
                }
                row.Height = 20 * 25;
                i++;
            }
            System.IO.MemoryStream file = new System.IO.MemoryStream();
            hssfworkbook.Write(file);
            file.Seek(0, System.IO.SeekOrigin.Begin);
            File.Delete(path + "/Files/" + fileName);

            return file;

        }


        public static MemoryStream BillProDetailsToExcel1(Models.MVC.BillProDetailsModel m)
        {
            IWorkbook hssfworkbook = null;
            ISheet sheet1 = null;
            string path = System.AppDomain.CurrentDomain.BaseDirectory.ToString();
            var fileName = Guid.NewGuid().ToString() + ".xls";
            File.Copy(path + "/Files/H₂烧结特殊工序首件检验记录.xls", path + "/Files/" + fileName, true);
            FileStream fs = new FileStream(path + "/Files/" + fileName, FileMode.Open, FileAccess.ReadWrite);
            hssfworkbook = new HSSFWorkbook(fs);
            sheet1 = hssfworkbook.GetSheetAt(0);


            sheet1.GetRow(1).GetCell(0).SetCellValue("产品型号：" + m.Field1);
            sheet1.GetRow(1).GetCell(5).SetCellValue("生产批号：" + m.Field2);
            sheet1.GetRow(2).GetCell(0).SetCellValue("烧结设备型号：" + m.Field3);
            sheet1.GetRow(2).GetCell(5).SetCellValue("编号/有效期：" + m.Field4);
            sheet1.GetRow(3).GetCell(0).SetCellValue("测试设备型号：" + m.Field5);
            sheet1.GetRow(3).GetCell(5).SetCellValue("编号/有效期：" + m.Field6);
            sheet1.GetRow(5).GetCell(1).SetCellValue(m.Field7);
            sheet1.GetRow(5).GetCell(5).SetCellValue(m.Field8);
            sheet1.GetRow(5).GetCell(6).SetCellValue(m.Field9);
            sheet1.GetRow(5).GetCell(7).SetCellValue(m.Field10);

            sheet1.GetRow(13).GetCell(0).SetCellValue("首件样品第 " + m.Field30 + " 次提交");

            sheet1.GetRow(15).GetCell(1).SetCellValue(m.Field11);
            sheet1.GetRow(15).GetCell(2).SetCellValue(m.Field12);
            sheet1.GetRow(15).GetCell(4).SetCellValue(m.Field13);
            sheet1.GetRow(15).GetCell(7).SetCellValue(m.Field14);

            sheet1.GetRow(17).GetCell(1).SetCellValue(m.Field15);
            sheet1.GetRow(17).GetCell(2).SetCellValue(m.Field16);
            sheet1.GetRow(17).GetCell(4).SetCellValue(m.Field17);
            sheet1.GetRow(17).GetCell(7).SetCellValue(m.Field18);

            sheet1.GetRow(19).GetCell(1).SetCellValue(m.Field19);
            sheet1.GetRow(19).GetCell(2).SetCellValue(m.Field20);
            sheet1.GetRow(19).GetCell(4).SetCellValue("剪切强度数据（kgF）：" + m.Field21);
            sheet1.GetRow(19).GetCell(7).SetCellValue(m.Field22);

            sheet1.GetRow(21).GetCell(1).SetCellValue("合格判据：剪切强度≥ " + m.Field23+ " kgF");
            sheet1.GetRow(21).GetCell(6).SetCellValue("基板面积：" + m.Field24 + " mm × " + m.Field25 + " mm");

            sheet1.GetRow(23).GetCell(0).SetCellValue("检验结论：" + m.Field26);

            sheet1.GetRow(24).GetCell(0).SetCellValue("检验员：" + m.Field27);
            sheet1.GetRow(24).GetCell(5).SetCellValue("日   期：" + m.Field28);
            sheet1.GetRow(25).GetCell(1).SetCellValue(m.Field29);

            System.IO.MemoryStream file = new System.IO.MemoryStream();
            hssfworkbook.Write(file);
            file.Seek(0, System.IO.SeekOrigin.Begin);
            File.Delete(path + "/Files/" + fileName);

            return file;

        }
        public static MemoryStream BillProDetailsToExcel2(Models.MVC.BillProDetailsModel m)
        {
            IWorkbook hssfworkbook = null;
            ISheet sheet1 = null;
            string path = System.AppDomain.CurrentDomain.BaseDirectory.ToString();
            var fileName = Guid.NewGuid().ToString() + ".xls";
            File.Copy(path + "/Files/超声键合关键工序首件检验记录.xls", path + "/Files/" + fileName, true);
            FileStream fs = new FileStream(path + "/Files/" + fileName, FileMode.Open, FileAccess.ReadWrite);
            hssfworkbook = new HSSFWorkbook(fs);
            sheet1 = hssfworkbook.GetSheetAt(0);


            sheet1.GetRow(1).GetCell(0).SetCellValue("产品型号：" + m.Field1);
            sheet1.GetRow(1).GetCell(6).SetCellValue("生产批号：" + m.Field2);
            sheet1.GetRow(2).GetCell(0).SetCellValue("键合设备型号：" + m.Field3);
            sheet1.GetRow(2).GetCell(6).SetCellValue("编号/有效期：" + m.Field4);
            sheet1.GetRow(3).GetCell(0).SetCellValue("测试设备型号：" + m.Field5);
            sheet1.GetRow(3).GetCell(6).SetCellValue("编号/有效期：" + m.Field6);

            sheet1.GetRow(6).GetCell(1).SetCellValue(m.Field7);
            sheet1.GetRow(6).GetCell(3).SetCellValue(m.Field8);
            sheet1.GetRow(6).GetCell(4).SetCellValue(m.Field9);
            sheet1.GetRow(6).GetCell(6).SetCellValue(m.Field10);
            sheet1.GetRow(6).GetCell(7).SetCellValue(m.Field11);

            sheet1.GetRow(7).GetCell(0).SetCellValue("首件样品第 "+m.Field12+" 次提交");

            sheet1.GetRow(9).GetCell(1).SetCellValue(m.Field13);
            sheet1.GetRow(9).GetCell(2).SetCellValue(m.Field14);
            sheet1.GetRow(9).GetCell(4).SetCellValue(m.Field15);
            sheet1.GetRow(9).GetCell(8).SetCellValue(m.Field16);

            sheet1.GetRow(10).GetCell(1).SetCellValue(m.Field17);
            sheet1.GetRow(10).GetCell(2).SetCellValue(m.Field18);
            sheet1.GetRow(10).GetCell(4).SetCellValue(m.Field19);
            sheet1.GetRow(10).GetCell(8).SetCellValue(m.Field20);

            sheet1.GetRow(11).GetCell(1).SetCellValue(m.Field21);
            sheet1.GetRow(11).GetCell(2).SetCellValue(m.Field22);
            sheet1.GetRow(11).GetCell(4).SetCellValue(m.Field23);
            sheet1.GetRow(11).GetCell(8).SetCellValue(m.Field24);

            sheet1.GetRow(12).GetCell(1).SetCellValue(m.Field25);
            sheet1.GetRow(12).GetCell(2).SetCellValue(m.Field26);
            sheet1.GetRow(12).GetCell(4).SetCellValue("键合强度数据（gF）：" + m.Field27);
            sheet1.GetRow(12).GetCell(8).SetCellValue(m.Field28);


            sheet1.GetRow(14).GetCell(1).SetCellValue("合格判据：键合强度≥" + m.Field29 + " gF");
            sheet1.GetRow(14).GetCell(6).SetCellValue("铝丝直径：：" + m.Field30 + " µm");

            sheet1.GetRow(15).GetCell(0).SetCellValue("检验结论：" + m.Field31);

            sheet1.GetRow(16).GetCell(0).SetCellValue("检验员：" + m.Field32);
            sheet1.GetRow(16).GetCell(5).SetCellValue("日   期：" + m.Field33);
            sheet1.GetRow(17).GetCell(1).SetCellValue(m.Field34);

            System.IO.MemoryStream file = new System.IO.MemoryStream();
            hssfworkbook.Write(file);
            file.Seek(0, System.IO.SeekOrigin.Begin);
            File.Delete(path + "/Files/" + fileName);

            return file;

        }
        public static MemoryStream BillProDetailsToExcel3(Models.MVC.BillProDetailsModel m)
        {
            IWorkbook hssfworkbook = null;
            ISheet sheet1 = null;
            string path = System.AppDomain.CurrentDomain.BaseDirectory.ToString();
            var fileName = Guid.NewGuid().ToString() + ".xls";
            File.Copy(path + "/Files/储能环焊特殊工序首件检验记录.xls", path + "/Files/" + fileName, true);
            FileStream fs = new FileStream(path + "/Files/" + fileName, FileMode.Open, FileAccess.ReadWrite);
            hssfworkbook = new HSSFWorkbook(fs);
            sheet1 = hssfworkbook.GetSheetAt(0);


            sheet1.GetRow(1).GetCell(0).SetCellValue("产品型号：" + m.Field1);
            sheet1.GetRow(1).GetCell(5).SetCellValue("生产批号：" + m.Field2);
            sheet1.GetRow(2).GetCell(0).SetCellValue("封帽设备型号：" + m.Field3);
            sheet1.GetRow(2).GetCell(5).SetCellValue("编号/有效期：" + m.Field4);

            sheet1.GetRow(4).GetCell(3).SetCellValue(m.Field5);
            sheet1.GetRow(4).GetCell(6).SetCellValue(m.Field6);

            sheet1.GetRow(7).GetCell(3).SetCellValue(m.Field7);
            sheet1.GetRow(7).GetCell(5).SetCellValue(m.Field8);
            sheet1.GetRow(7).GetCell(7).SetCellValue(m.Field9);

            sheet1.GetRow(11).GetCell(2).SetCellValue(m.Field10);
            sheet1.GetRow(11).GetCell(4).SetCellValue(m.Field11);
            sheet1.GetRow(11).GetCell(6).SetCellValue(m.Field12);
            sheet1.GetRow(11).GetCell(7).SetCellValue(m.Field13);

            sheet1.GetRow(13).GetCell(2).SetCellValue(m.Field14);
            sheet1.GetRow(13).GetCell(4).SetCellValue(m.Field15);
            sheet1.GetRow(13).GetCell(6).SetCellValue(m.Field16);
            sheet1.GetRow(13).GetCell(7).SetCellValue(m.Field17);


            sheet1.GetRow(15).GetCell(0).SetCellValue("检验结论：" + m.Field18);

            sheet1.GetRow(16).GetCell(0).SetCellValue("检验员：" + m.Field19);
            sheet1.GetRow(16).GetCell(5).SetCellValue("日   期：" + m.Field20);
            sheet1.GetRow(17).GetCell(1).SetCellValue(m.Field21);

            System.IO.MemoryStream file = new System.IO.MemoryStream();
            hssfworkbook.Write(file);
            file.Seek(0, System.IO.SeekOrigin.Begin);
            File.Delete(path + "/Files/" + fileName);

            return file;

        }
        public static MemoryStream BillProDetailsToExcel4(Models.MVC.BillProDetailsModel m)
        {
            IWorkbook hssfworkbook = null;
            ISheet sheet1 = null;
            string path = System.AppDomain.CurrentDomain.BaseDirectory.ToString();
            var fileName = Guid.NewGuid().ToString() + ".xls";
            File.Copy(path + "/Files/工序专检验记录.xls", path + "/Files/" + fileName, true);
            FileStream fs = new FileStream(path + "/Files/" + fileName, FileMode.Open, FileAccess.ReadWrite);
            hssfworkbook = new HSSFWorkbook(fs);
            sheet1 = hssfworkbook.GetSheetAt(0);


            sheet1.GetRow(1).GetCell(0).SetCellValue("产品型号：" + m.Field1);
            sheet1.GetRow(1).GetCell(4).SetCellValue("生产批号：" + m.Field2);
            sheet1.GetRow(2).GetCell(0).SetCellValue("烧结设备型号：" + m.Field3);
            sheet1.GetRow(2).GetCell(4).SetCellValue("编号/有效期：" + m.Field4);
            sheet1.GetRow(3).GetCell(0).SetCellValue("键合设备型号：" + m.Field5);
            sheet1.GetRow(3).GetCell(4).SetCellValue("编号/有效期：" + m.Field6);
            sheet1.GetRow(4).GetCell(0).SetCellValue("测试设备型号：" + m.Field7);
            sheet1.GetRow(4).GetCell(4).SetCellValue("编号/有效期：" + m.Field8);


            sheet1.GetRow(6).GetCell(1).SetCellValue(m.Field9);
            sheet1.GetRow(6).GetCell(2).SetCellValue(m.Field10);
            sheet1.GetRow(7).GetCell(3).SetCellValue(m.Field11);
            sheet1.GetRow(7).GetCell(7).SetCellValue(m.Field12);
            sheet1.GetRow(8).GetCell(7).SetCellValue(m.Field13);
            sheet1.GetRow(9).GetCell(7).SetCellValue(m.Field14);
            sheet1.GetRow(10).GetCell(7).SetCellValue(m.Field15);

            sheet1.GetRow(11).GetCell(1).SetCellValue("合格判据：键合强度≥ " + m.Field16 + " gF");
            sheet1.GetRow(11).GetCell(4).SetCellValue("铝丝直径： "+m.Field17+ " μm");

            sheet1.GetRow(12).GetCell(1).SetCellValue(m.Field18);
            sheet1.GetRow(12).GetCell(2).SetCellValue(m.Field19);
            sheet1.GetRow(13).GetCell(3).SetCellValue(m.Field20);
            sheet1.GetRow(13).GetCell(5).SetCellValue(m.Field21);

            sheet1.GetRow(16).GetCell(1).SetCellValue(@"粘结材料：" + m.Field22 + @"
合格判据：剪切强度≥ " + m.Field23 + @" kgF");
            sheet1.GetRow(16).GetCell(5).SetCellValue("芯片面积：" + m.Field24 + " mm × " + m.Field25 + " mm");

            sheet1.GetRow(19).GetCell(0).SetCellValue("检验结论：" + m.Field26);

            sheet1.GetRow(20).GetCell(0).SetCellValue("检验员：" + m.Field27);
            sheet1.GetRow(20).GetCell(5).SetCellValue("日   期：" + m.Field28);
            sheet1.GetRow(21).GetCell(1).SetCellValue(m.Field29);

            System.IO.MemoryStream file = new System.IO.MemoryStream();
            hssfworkbook.Write(file);
            file.Seek(0, System.IO.SeekOrigin.Begin);
            File.Delete(path + "/Files/" + fileName);

            return file;

        }
        public static MemoryStream BillProDetailsToExcel5(Models.MVC.BillProDetailsModel m)
        {
            IWorkbook hssfworkbook = null;
            ISheet sheet1 = null;
            string path = System.AppDomain.CurrentDomain.BaseDirectory.ToString();
            var fileName = Guid.NewGuid().ToString() + ".xls";
            File.Copy(path + "/Files/金丝球键合关键工序首件检验记录.xls", path + "/Files/" + fileName, true);
            FileStream fs = new FileStream(path + "/Files/" + fileName, FileMode.Open, FileAccess.ReadWrite);
            hssfworkbook = new HSSFWorkbook(fs);
            sheet1 = hssfworkbook.GetSheetAt(0);


            sheet1.GetRow(1).GetCell(0).SetCellValue("产品型号：" + m.Field1);
            sheet1.GetRow(1).GetCell(5).SetCellValue("生产批号：" + m.Field2);
            sheet1.GetRow(2).GetCell(0).SetCellValue("键合设备型号：" + m.Field3);
            sheet1.GetRow(2).GetCell(5).SetCellValue("编号/有效期：" + m.Field4);
            sheet1.GetRow(3).GetCell(0).SetCellValue("测试设备型号：" + m.Field5);
            sheet1.GetRow(3).GetCell(5).SetCellValue("编号/有效期：" + m.Field6);

            sheet1.GetRow(6).GetCell(1).SetCellValue(m.Field7);
            sheet1.GetRow(6).GetCell(3).SetCellValue(m.Field8);
            sheet1.GetRow(6).GetCell(4).SetCellValue(m.Field9);
            sheet1.GetRow(6).GetCell(6).SetCellValue(m.Field10);
            sheet1.GetRow(6).GetCell(7).SetCellValue(m.Field11);

            sheet1.GetRow(7).GetCell(0).SetCellValue("首件样品第 " + m.Field12 + " 次提交");

            sheet1.GetRow(9).GetCell(1).SetCellValue(m.Field13);
            sheet1.GetRow(9).GetCell(2).SetCellValue(m.Field14);
            sheet1.GetRow(9).GetCell(4).SetCellValue(m.Field15);
            sheet1.GetRow(9).GetCell(8).SetCellValue(m.Field16);

            sheet1.GetRow(10).GetCell(1).SetCellValue(m.Field17);
            sheet1.GetRow(10).GetCell(2).SetCellValue(m.Field18);
            sheet1.GetRow(10).GetCell(4).SetCellValue(m.Field19);
            sheet1.GetRow(10).GetCell(8).SetCellValue(m.Field20);

            sheet1.GetRow(11).GetCell(1).SetCellValue(m.Field21);
            sheet1.GetRow(11).GetCell(2).SetCellValue(m.Field22);
            sheet1.GetRow(11).GetCell(4).SetCellValue(m.Field23);
            sheet1.GetRow(11).GetCell(8).SetCellValue(m.Field24);

            sheet1.GetRow(12).GetCell(1).SetCellValue(m.Field25);
            sheet1.GetRow(12).GetCell(2).SetCellValue(m.Field26);
            sheet1.GetRow(12).GetCell(4).SetCellValue("键合强度数据（gF）：" + m.Field27);
            sheet1.GetRow(12).GetCell(8).SetCellValue(m.Field28);


            sheet1.GetRow(14).GetCell(1).SetCellValue("合格判据：键合强度≥" + m.Field29 + " gF");
            sheet1.GetRow(14).GetCell(6).SetCellValue("铝丝直径：：" + m.Field30 + " µm");

            sheet1.GetRow(15).GetCell(0).SetCellValue("检验结论：" + m.Field31);

            sheet1.GetRow(16).GetCell(0).SetCellValue("检验员：" + m.Field32);
            sheet1.GetRow(16).GetCell(5).SetCellValue("日   期：" + m.Field33);
            sheet1.GetRow(17).GetCell(1).SetCellValue(m.Field34);

            System.IO.MemoryStream file = new System.IO.MemoryStream();
            hssfworkbook.Write(file);
            file.Seek(0, System.IO.SeekOrigin.Begin);
            File.Delete(path + "/Files/" + fileName);

            return file;

        }
        public static MemoryStream BillProDetailsToExcel6(Models.MVC.BillProDetailsModel m)
        {
            IWorkbook hssfworkbook = null;
            ISheet sheet1 = null;
            string path = System.AppDomain.CurrentDomain.BaseDirectory.ToString();
            var fileName = Guid.NewGuid().ToString() + ".xls";
            File.Copy(path + "/Files/平行缝焊特殊工序首件检验记录.xls", path + "/Files/" + fileName, true);
            FileStream fs = new FileStream(path + "/Files/" + fileName, FileMode.Open, FileAccess.ReadWrite);
            hssfworkbook = new HSSFWorkbook(fs);
            sheet1 = hssfworkbook.GetSheetAt(0);


            sheet1.GetRow(1).GetCell(0).SetCellValue("产品型号：" + m.Field1);
            sheet1.GetRow(1).GetCell(5).SetCellValue("生产批号：" + m.Field2);
            sheet1.GetRow(2).GetCell(0).SetCellValue("封盖设备型号：：" + m.Field3);
            sheet1.GetRow(2).GetCell(5).SetCellValue("编号/有效期：" + m.Field4);

            sheet1.GetRow(4).GetCell(2).SetCellValue(m.Field5);
            sheet1.GetRow(4).GetCell(5).SetCellValue(m.Field6);
            sheet1.GetRow(4).GetCell(9).SetCellValue(m.Field7);

            sheet1.GetRow(6).GetCell(2).SetCellValue(m.Field8);
            sheet1.GetRow(6).GetCell(3).SetCellValue(m.Field9);
            sheet1.GetRow(6).GetCell(5).SetCellValue(m.Field10);
            sheet1.GetRow(6).GetCell(7).SetCellValue(m.Field11);
            sheet1.GetRow(6).GetCell(9).SetCellValue(m.Field12);
            sheet1.GetRow(6).GetCell(10).SetCellValue(m.Field13);

            sheet1.GetRow(8).GetCell(1).SetCellValue(m.Field14);
            sheet1.GetRow(8).GetCell(6).SetCellValue(m.Field15);

            sheet1.GetRow(9).GetCell(0).SetCellValue("首件样品第 " + m.Field16 + " 次提交");

            sheet1.GetRow(11).GetCell(1).SetCellValue(m.Field17);
            sheet1.GetRow(11).GetCell(2).SetCellValue(m.Field18);
            sheet1.GetRow(11).GetCell(5).SetCellValue(m.Field19);
            sheet1.GetRow(11).GetCell(9).SetCellValue(m.Field20);

            sheet1.GetRow(12).GetCell(1).SetCellValue(m.Field21);
            sheet1.GetRow(12).GetCell(2).SetCellValue(m.Field22);
            sheet1.GetRow(12).GetCell(5).SetCellValue(m.Field23);
            sheet1.GetRow(12).GetCell(9).SetCellValue(m.Field24);


            sheet1.GetRow(13).GetCell(0).SetCellValue("检验结论：" + m.Field25);

            sheet1.GetRow(14).GetCell(0).SetCellValue("检验员：" + m.Field26);
            sheet1.GetRow(14).GetCell(4).SetCellValue("日　期：" + m.Field27);
            sheet1.GetRow(15).GetCell(1).SetCellValue(m.Field28);

            System.IO.MemoryStream file = new System.IO.MemoryStream();
            hssfworkbook.Write(file);
            file.Seek(0, System.IO.SeekOrigin.Begin);
            File.Delete(path + "/Files/" + fileName);

            return file;

        }

    }
}